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