Group by on %like%

What is the output you are trying to achieve?

Could someone look into this?
Since I do not believe we should condone this.
This is what I got when I sent a message to the list.
At least, I do not like these personally.
If I was wrong with this, then I apologise up front.
If I need to send these kinds of remarks elsewhere, please provide me
with the correct information

Regards,

Serge Fonville

On Fri, Jul 3, 2009 at 7:44 PM, Jennifer Trey <jennifer.trey@gmail.com>wrote:

Hi,

I would like to run a query and group several rows based on a phone number.

However, the same phone number might have a prefix on occasion, example :

name | phone_number
----------------------
james | 123456
james | 00441234556
james | 555666
sarah | 567890
sarah | 567890

as you can see, the first 2 James seems to belong together.

running

select name, phone_number from relation group by name, phone_number

would not reflect this.

I don't think there is a way to run something similar to this :

select name, phone_number from relation group by name, %phone_number%
// or similar

However, I believe there is a way, so I would like to here it from you :)

Functions, sums .. please let me know..

Thank you in advance / Jennifer

You could run a sub-select first to get your results and then group on that,
such as:

SELECT name, pn
FROM
(SELECT name, substring(phone_number from length(phone_number)-7) AS pn
FROM relation
WHERE phone_number LIKE '%1234%') AS r
GROUP BY name,pn

The substring bit is the part you will have to work out in order to make
sure you get the correct rows returning you are looking for. This is just an
example :)

Regards,
GF

On Fri, Jul 3, 2009 at 8:32 PM, Guy Flaherty <naoshika@gmail.com> wrote:

On Fri, Jul 3, 2009 at 7:44 PM, Jennifer Trey <jennifer.trey@gmail.com>wrote:

Hi,

I would like to run a query and group several rows based on a phone
number.

However, the same phone number might have a prefix on occasion, example :

name | phone_number
----------------------
james | 123456
james | 00441234556
james | 555666
sarah | 567890
sarah | 567890

as you can see, the first 2 James seems to belong together.

running

select name, phone_number from relation group by name, phone_number

would not reflect this.

I don't think there is a way to run something similar to this :

select name, phone_number from relation group by name, %phone_number%
// or similar

However, I believe there is a way, so I would like to here it from you :)

Functions, sums .. please let me know..

Thank you in advance / Jennifer

You could run a sub-select first to get your results and then group on
that, such as:

SELECT name, pn
FROM
(SELECT name, substring(phone_number from length(phone_number)-7) AS pn
FROM relation
WHERE phone_number LIKE '%1234%') AS r
GROUP BY name,pn

Blah, having said that, you are probably looking for something more like
this:

SELECT "name", substring(phone_number from length(phone_number)-7) AS pn
FROM relation
GROUP BY name,2

GF

Hi,

Le 3 juil. 09 à 11:44, Jennifer Trey a écrit :

I would like to run a query and group several rows based on a phone
number.
However, the same phone number might have a prefix on occasion,
example :

name | phone_number
----------------------
james | 123456
james | 00441234556
as you can see, the first 2 James seems to belong together.

What I would do is provide a normalize_phone_number(phone_number
text), such as it returns the same phone number when given a number
with or without international prefix.

Then you
SELECT name, normalize_phone_number(phone_numer)
FROM relation
GROUP BY 1, 2;

Now you're left with deciding if you prefer to normalize with the
prefix or with it stripped, and to invent an automated way to detect
international prefixes. The so called prefix project might help you do
this if you have a table of known prefixes to strip (or recognize):
http://prefix.projects.postgresql.org/
http://prefix.projects.postgresql.org/prefix-1.0~rc1.tar.gz

CREATE OR REPLACE FUNCTION normalize_phone_number(text)
RETURNS text
LANGUAGE PLpgSQL
STABLE
AS $f$
DECLARE
v_prefix text;
BEGIN
SELECT prefix
INTO v_prefix
FROM international_prefixes
WHERE prefix @> $1;

IF FOUND
THEN
-- we strip the prefix to normalize the phone number
RETURN substring($1 from length(v_prefix));
ELSE
RETURN $1;
END IF;
END;
$f$;

Note: I typed the function definition directly into the Mail composer,
bugs are yours :)

Regards,
--
dim

CC'ing to WWW. Seems we have a vacation reply that is an advertisement.

---------------------------------------------------------------------------

Serge Fonville wrote:

Could someone look into this?
Since I do not believe we should condone this.
This is what I got when I sent a message to the list.
At least, I do not like these personally.
If I was wrong with this, then I apologise up front.
If I need to send these kinds of remarks elsewhere, please provide me
with the correct information

Regards,

Serge Fonville

On Fri, Jul 3, 2009 at 11:52 AM, <fernstudios@hotmail.com> wrote:

Dear Friends:

?????????? We are a large wholesaler who mainly sell electrical products
such as laptop,TV,digital camera, mobile, Digital Video, Mp4, GPS, and so
on. And our official web is? fcxqrz.com? We offer you the products with the
best quality and price .All the items on our website are brand new in sealed
factory box and offered warranty by the original manufactures .

Email:?? fcxqrz01@188.com

MSN? :? fcxqrz@hotmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

It doesn't even seem to be a real company. I visited the website, just
out of curiosity and they state they are a real company and are
reliable. To me that sounds like they are not.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

This is what I got when I sent a message to the list.

...

On Fri, Jul 3, 2009 at 11:52 AM, <fernstudios@hotmail.com> wrote:

Dear Friends:

?????????? We are a large wholesaler who mainly sell electrical products

Unsubscribed from -general.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200907031009
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAkpOESsACgkQvJuQZxSWSsjeBgCg/Ygg1f889v5HBMBN1sq3E566
hdkAnieek1T4eZjrSjUQcAyZ4+Glcmwc
=Ds7j
-----END PGP SIGNATURE-----

Hello,

Le 3/07/09 12:53, Dimitri Fontaine a �crit :

Hi,

Le 3 juil. 09 � 11:44, Jennifer Trey a �crit :

I would like to run a query and group several rows based on a phone
number.
However, the same phone number might have a prefix on occasion, example :

name | phone_number
----------------------
james | 123456
james | 00441234556
as you can see, the first 2 James seems to belong together.

What I would do is provide a normalize_phone_number(phone_number text),
such as it returns the same phone number when given a number with or
without international prefix.

Then you
SELECT name, normalize_phone_number(phone_numer)
FROM relation
GROUP BY 1, 2;
[...]

The solution suggested by Dimitri Fontaine and based on a customized
function for normalizing phone numbers seems to be a clean one. All the
power is contained in the normalize_phone_number() implementation.

The following query may be an alternative solution that does not require
any tier function except the classic aggregative ones (COUNT(), SUM()):

SELECT P3.name, P3.phone_number
FROM (
SELECT P1.name, P1.phone_number, (
CASE WHEN CHAR_LENGTH(P1.phone_number) >= CHAR_LENGTH(P2.phone_number)
THEN 1
ELSE 0
END
) AS gec
FROM (
SELECT P01.name, P01.phone_number
FROM pnd AS P01
GROUP BY P01.name, P01.phone_number
) AS P1 INNER JOIN (
SELECT P02.name, P02.phone_number
FROM pnd AS P02
GROUP BY P02.name, P02.phone_number
) AS P2
ON P1.name = P2.name
AND (
CASE WHEN CHAR_LENGTH(P1.phone_number) >= CHAR_LENGTH(P2.phone_number)
THEN P1.phone_number LIKE ('%'||P2.phone_number)
ELSE P2.phone_number LIKE ('%'||P1.phone_number)
END
)
) AS P3
GROUP BY P3.name, P3.phone_number
HAVING COUNT(*) = SUM(P3.gec)

"pnd" is assumed to be the main table including "name" and
"phone_number" columns. "pnd" is directly used as a table source in
subqueries aliased P1 and P2 and only for those subqueries.

Assuming the starting values in the table "pnd" as following:

name | phone_number
----------------------
james | 123456
james | 0044123456
james | 555666
sarah | 567890
sarah | 567890

(notice that the phone_number of the 2nd row has been adjusted for
similarity to be effective between row 1 and row 2)

The resulting rows from the overall query will be:

name | phone_number
----------------------
james | 0044123456
james | 555666
sarah | 567890

The choice has been made here to keep the longuest phone_number for each
set of similar phone_numbers. The shortest could also be kept if desired.

The overall query implies a few subqueries. Subquery aliased P3 is a
join between P1 and P2, both corresponding to the same subquery. The
difference is in expressing the join conditions: i) on the commun column
"name"; and ii) on the likelihood between phone numbers according to the
length of these latter. Function CHAR_LENGTH() is used instead of
LENGTH() because the first renders the real number of characters whereas
the second gives the number of bytes used to encode the argument.

Table P3 is composed of couples (X, Y) of "name" and "phone_number".
Each couple is associated to the number "gec" resulting from the
counting of phone_numbers Z similar to Y and with CHAR_LENGTH(Y) greater
or equal to CHAR_LENGTH(Z).

Eventually only the rows of P3 for which the sum of "gec" is equal to
the number of rows of P3 where the value of "phone_number" is the same
are kept.

Hoping this alternative solution will help a little (validated with
PostgreSQL 8.3.1).

Regards.

P-S: I think this question might also have interested the PgSQL-SQL
mailing list and posted there.

--
nha / Lyon / France.

Sorry for taking so long to respond. The prefix thingy is definetly
attractive for future development and I had already discovered them to be a
challenge. However, i have noticed all kinds of ways people tend to write
their number, including omitting the + or 00 .. so at this time, for this
task, I found Guys to be working pretty well.
Thanks all / Jen