Re: only best matches with ilike or regex matching

hi Chad,

thanks for responding.
Your solution points out to me that I my example
wasn't perfect in one way: it suggests that providers use
the same prefix list. But they don't. Sorry about that.

In reality this would be a more realistic example:

id | prefix  |  cost   |  timeframe | provider
----------------------------------------------
1  | ^31     |  0.02   |  1         | 1
2  | ^31     |  0.01   |  2         | 1
3  | ^31653  |  0.14   |  1         | 1
4  | ^31653  |  0.12   |  2         | 1
5  | ^31     |  0.03   |  1         | 2
6  | ^31     |  0.02   |  2         | 2
7  | ^316    |  0.15   |  1         | 2
8  | ^316    |  0.13   |  2         | 2

As you see, different providers divide up the possible
range of phone numbers in a different way.

Now your last query won't work because the subselect
will return the prefix from row 4, and this will not
match row 8. Can you offer another suggestion?

Ron

Chad Wagner schreef:
> On 1/17/07, *Ron Arts* <ron.arts@neonova.nl
> <mailto: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/