Skip site navigation (1) Skip section navigation (2)

Re: only best matches with ilike or regex matching

From: "Chad Wagner" <chad(dot)wagner(at)gmail(dot)com>
To: "Ron Arts" <ron(dot)arts(at)neonova(dot)nl>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: only best matches with ilike or regex matching
Date: 2007-01-17 23:11:05
Message-ID: 81961ff50701171511u548b2ce5k38f6e4656b31d017@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
On 1/17/07, Ron Arts <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/

In response to

Responses

pgsql-novice by date

Next:From: Ron ArtsDate: 2007-01-18 07:48:20
Subject: Re: only best matches with ilike or regex matching
Previous:From: Ron ArtsDate: 2007-01-17 22:20:24
Subject: only best matches with ilike or regex matching

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group