Re: only best matches with ilike or regex matching - Mailing list pgsql-novice

>
> I have a table containing phone destinations and pricing as follows:
>

SELECT * FROM tel ORDER BY cost;

SELECT * FROM tel WHERE timeframe = 2 ORDER BY cost;

SELECT * FROM tel t1
WHERE SUBSTR( '31653445566', 1, LENGTH( prefix ) ) = prefix
AND timeframe = 2
AND NOT EXISTS
( SELECT NULL FROM tel t2
  WHERE
  t1.timeframe = t2.timeframe
  AND t1.provider = t2.provider
  AND t1.prefix = SUBSTR( t2.prefix, 1, LENGTH( t1.prefix ) )
  AND LENGTH( t1.prefix ) < LENGTH( t2.prefix )
);

Produces

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

 id | prefix | cost | timeframe | provider
----+--------+------+-----------+----------
  2 | 31     | 0.01 |         2 |        1
  6 | 31     | 0.02 |         2 |        2
  4 | 31653  | 0.12 |         2 |        1
  8 | 316    | 0.13 |         2 |        2
(4 rows)

 id | prefix | cost | timeframe | provider
----+--------+------+-----------+----------
  4 | 31653  | 0.12 |         2 |        1
  8 | 316    | 0.13 |         2 |        2
(2 rows)

Is that what you meant? The longest match for a given provider?

Regards

Duncan
-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Ron Arts
Sent: 18 January 2007 07:48
To: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] 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/