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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Ron Arts | 2007-01-18 07:48:20 | Re: only best matches with ilike or regex matching |
Previous Message | Ron Arts | 2007-01-17 22:20:24 | only best matches with ilike or regex matching |