On 1/17/07,
Ron Arts <
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/
