Re: only best matches with ilike or regex matching - Mailing list pgsql-novice
From | Ron Arts |
---|---|
Subject | Re: only best matches with ilike or regex matching |
Date | |
Msg-id | 45AF5418.4030403@neonova.nl Whole thread Raw |
In response to | Re: only best matches with ilike or regex matching ("Duncan Garland" <duncan.garland@ntlworld.com>) |
List | pgsql-novice |
Hi Duncan, Yes. This works great. Now could this be done using regex matching as well? Ron Duncan Garland schreef: >> I have a table containing phone destinations and pricing as follows: >> > > SELECT * FROM tel ORDER BY cost; > > SELECT * FROM tel WHERE timeframe = 2 ORDER BY cost; > > SELECT * FROM tel t1 > WHERE SUBSTR( '31653445566', 1, LENGTH( prefix ) ) = prefix > AND timeframe = 2 > AND NOT EXISTS > ( SELECT NULL FROM tel t2 > WHERE > t1.timeframe = t2.timeframe > AND t1.provider = t2.provider > AND t1.prefix = SUBSTR( t2.prefix, 1, LENGTH( t1.prefix ) ) > AND LENGTH( t1.prefix ) < LENGTH( t2.prefix ) > ); > > Produces > > id | prefix | cost | timeframe | provider > ----+--------+------+-----------+---------- > 2 | 31 | 0.01 | 2 | 1 > 1 | 31 | 0.02 | 1 | 1 > 6 | 31 | 0.02 | 2 | 2 > 5 | 31 | 0.03 | 1 | 2 > 4 | 31653 | 0.12 | 2 | 1 > 8 | 316 | 0.13 | 2 | 2 > 3 | 31653 | 0.14 | 1 | 1 > 7 | 316 | 0.15 | 1 | 2 > (8 rows) > > id | prefix | cost | timeframe | provider > ----+--------+------+-----------+---------- > 2 | 31 | 0.01 | 2 | 1 > 6 | 31 | 0.02 | 2 | 2 > 4 | 31653 | 0.12 | 2 | 1 > 8 | 316 | 0.13 | 2 | 2 > (4 rows) > > id | prefix | cost | timeframe | provider > ----+--------+------+-----------+---------- > 4 | 31653 | 0.12 | 2 | 1 > 8 | 316 | 0.13 | 2 | 2 > (2 rows) > > Is that what you meant? The longest match for a given provider? > > Regards > > Duncan > -----Original Message----- > From: pgsql-novice-owner@postgresql.org > [mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Ron Arts > Sent: 18 January 2007 07:48 > To: pgsql-novice@postgresql.org > Subject: Re: [NOVICE] only best matches with ilike or regex matching > > > hi Chad, > > thanks for responding. > Your solution points out to me that I my example > wasn't perfect in one way: it suggests that providers use > the same prefix list. But they don't. Sorry about that. > > In reality this would be a more realistic example: > > id | prefix | cost | timeframe | provider > ---------------------------------------------- > 1 | ^31 | 0.02 | 1 | 1 > 2 | ^31 | 0.01 | 2 | 1 > 3 | ^31653 | 0.14 | 1 | 1 > 4 | ^31653 | 0.12 | 2 | 1 > 5 | ^31 | 0.03 | 1 | 2 > 6 | ^31 | 0.02 | 2 | 2 > 7 | ^316 | 0.15 | 1 | 2 > 8 | ^316 | 0.13 | 2 | 2 > > As you see, different providers divide up the possible > range of phone numbers in a different way. > > Now your last query won't work because the subselect > will return the prefix from row 4, and this will not > match row 8. Can you offer another suggestion? > > Ron > > Chad Wagner schreef: >> On 1/17/07, *Ron Arts* <ron.arts@neonova.nl >> <mailto:ron.arts@neonova.nl>> wrote: >> >> this is probably an SQL question instead of PostgreSQL but here it > goes. >> I have a table containing phone destinations and pricing as follows: >> >> prefix | cost | timeframe | provider >> ---------------------------------------- >> ^31 | 0.02 | 1 | 1 >> ^31 | 0.01 | 2 | 1 >> ^31653 | 0.14 | 1 | 1 >> ^31653 | 0.12 | 2 | 1 >> ^31 | 0.03 | 1 | 2 >> ^31 | 0.02 | 2 | 2 >> ^31653 | 0.15 | 1 | 2 >> ^31653 | 0.13 | 2 | 2 >> >> where timeframe=2 means weekends. >> >> For a given phonenumber I need to get the list of providers with the >> cheapest one first. Suppose the target phonenumber is 31653445566, >> and timeframe is 2: >> >> prefix | cost | timeframe | provider >> ---------------------------------------- >> ^31653 | 0.12 | 2 | 1 >> ^31653 | 0.13 | 2 | 2 >> >> But I cannot find a query to get this result. I only want >> the ^31653 rows, and not the ^31 rows, but these both match >> a 'where'31653445566' ~ prefix' clause. Using distinct does not >> work as well. >> >> >> This is close... >> >> # select * from phonerates where '^316534455665' like prefix || '%' and >> timeframe = 2 order by length(prefix) desc, cost asc limit 1; >> prefix | cost | timeframe | provider >> --------+------+-----------+---------- >> ^31653 | 0.12 | 2 | 1 >> >> If you want exactly as you indicated above, then... >> >> select * >> from phonerates >> where timeframe = 2 >> and prefix = (select prefix >> from phonerates >> where '^316534455665' like prefix || '%' >> order by length(prefix) desc >> limit 1) >> order by cost; >> >> prefix | cost | timeframe | provider >> --------+------+-----------+---------- >> ^31653 | 0.12 | 2 | 1 >> ^31653 | 0.13 | 2 | 2 >> >> >> >> -- >> Chad >> http://www.postgresqlforums.com/ > > -- NeoNova BV, The Netherlands Professional internet and VoIP solutions http://www.neonova.nl Kruislaan 419 1098 VA Amsterdam info: 020-5628292 servicedesk: 020-5628292 fax: 020-5628291 KvK Amsterdam 34151241 The following disclaimer applies to this email: http://www.neonova.nl/maildisclaimer
Attachment
pgsql-novice by date: