Re: only best matches with ilike or regex matching

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(at)postgresql(dot)org
> [mailto:pgsql-novice-owner(at)postgresql(dot)org]On Behalf Of Ron Arts
> Sent: 18 January 2007 07:48
> To: pgsql-novice(at)postgresql(dot)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(dot)arts(at)neonova(dot)nl
>> <mailto:ron(dot)arts(at)neonova(dot)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