Re: 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: Re: only best matches with ilike or regex matching
Date: 2007-01-18 07:48:20
Message-ID: 45AF2644.6010405@neonova.nl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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(dot)arts(at)neonova(dot)nl
> <mailto:ron(dot)arts(at)neonova(dot)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/

In response to

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Duncan Garland 2007-01-18 09:41:05 Re: only best matches with ilike or regex matching
Previous Message Chad Wagner 2007-01-17 23:11:05 Re: only best matches with ilike or regex matching