On Fri, Jul 3, 2009 at 8:32 PM, Guy Flaherty <naosh...@gmail.com> wrote:
> > > On Fri, Jul 3, 2009 at 7:44 PM, Jennifer Trey <jennifer.t...@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
