Hi Mario, over - partition by will help. Iy that syntax is uncomfortable you can use multiple joins, using maximum length or max(prefix::int) but that will slow down the process. select over - partition by is fastest solution i think.
On Sat, Dec 10, 2011 at 11:24 PM, Mario Splivalo <mario.spliv...@megafon.hr>wrote: > I have a table called 'calls' which holds 'call detail records'. Let's > assume the table looks like this: > > CREATE TABLE cdr ( > call_id serial, > phone_number text > ); > > And I have a table with country call prefixes, that looks like this: > > CREATE TABLE prefixes ( > prefix text, > country text > ); > > And now some test data: > > INSERT INTO prefixes VALUES ('1', 'USA'); > INSERT INTO prefixes VALUES ('44', 'UK'); > INSERT INTO prefixes VALUES ('385', 'Croatia'); > INSERT INTO prefixes VALUES ('387', 'Bosnia'); > INSERT INTO prefixes VALUES ('64', 'New Zeland'); > INSERT INTO prefixes VALUES ('642', 'New Zeland Mobile'); > INSERT INTO calls VALUES (1, '11952134451'); > INSERT INTO calls VALUES (2, '448789921342'); > INSERT INTO calls VALUES (3, '385914242232'); > INSERT INTO calls VALUES (4, '385914242232'); > INSERT INTO calls VALUES (5, '645122231241'); > INSERT INTO calls VALUES (6, '444122523421'); > INSERT INTO calls VALUES (7, '64212125452'); > INSERT INTO calls VALUES (8, '1837371211'); > INSERT INTO calls VALUES (9, '11952134451'); > INSERT INTO calls VALUES (10, '448789921342'); > INSERT INTO calls VALUES (11, '385914242232'); > INSERT INTO calls VALUES (12, '385914242232'); > INSERT INTO calls VALUES (13, '645122231241'); > INSERT INTO calls VALUES (14, '4441232523421'); > INSERT INTO calls VALUES (15, '64112125452'); > INSERT INTO calls VALUES (16, '1837371211'); > > > Now, if I want to have a 'join' between those two tables, here is what I > am doing right now: > > SELECT > call_id, > phone_number, > (SELECT > country > FROM > prefixes > WHERE > calls.phone_number LIKE prefix || '%' > ORDER BY > length(prefix) DESC LIMIT 1 > ) AS country > FROM calls; > > > Is there a way I could use join here? I can do something like: > > SELECT ... FROM calls JOIN prefixes ON calls.phone_number LIKE prefix || > '%' > > but I'd get duplicate rows there (for instance, for New Zeland calls, > from my test data). > > Or should I add 'prefix' field to the calls table, and then do a inner > join with prefixes table? > > Mario > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- --------------------------------------- Viktor Bojović --------------------------------------- Wherever I go, Murphy goes with me