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

only best matches with ilike or regex matching

From: Ron Arts <ron(dot)arts(at)neonova(dot)nl>
To: pgsql-novice(at)postgresql(dot)org
Subject: only best matches with ilike or regex matching
Date: 2007-01-17 22:20:24
Message-ID: 45AEA128.8070306@neonova.nl (view raw or flat)
Thread:
Lists: pgsql-novice
Hi,

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.

Please help,

Thanks,
Ron


Responses

pgsql-novice by date

Next:From: Chad WagnerDate: 2007-01-17 23:11:05
Subject: Re: only best matches with ilike or regex matching
Previous:From: joe speigleDate: 2007-01-17 16:19:01
Subject: COMMENT on "osc_zones" ...... ERROR at the opening quotation

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