only best matches with ilike or regex matching

[Ron Arts <ron(dot)arts(at)neonova(dot)nl>]

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

Attachment: smime.p7s
Description: S/MIME Cryptographic Signature