Postgres Pro
Downloads
Home   >   mailing lists

Thread: Most specific match using between

Most specific match using between

From
mmiranda@americatel.com.sv
Date:
Hi people, is there a swtich or something to instruct a between statement
hat it must perform a most specific match ?
These are the details, i have these table

 name        | start        | end
---------------+---------------+-----------------
general     | 2660000  | 2669999
specific     | 2660124  | 2660124
(2 rows)


unsing the query:  select name where '2660124' between start and end

i got:


name
---------
general
specific


How can i get only specific?


thanks

Re: Most specific match using between

From
Scott Marlowe
Date:
On Thu, 2005-04-21 at 10:51, mmiranda@americatel.com.sv wrote:
> Hi people, is there a swtich or something to instruct a between statement
> hat it must perform a most specific match ?
> These are the details, i have these table
>
>  name        | start        | end
> ---------------+---------------+-----------------
> general     | 2660000  | 2669999
> specific     | 2660124  | 2660124
> (2 rows)
>
>
> unsing the query:  select name where '2660124' between start and end
>
> i got:
>
>
> name
> ---------
> general
> specific
>
>
> How can i get only specific?

Do you want the ONE row that's closest, or a set of rows that are fairly
close?  Not sure what your specification it exactly, but how about:

select name where '2660124' between start and end order by abs
(start-end) limit 1;

Re: Most specific match using between

From
Steve Atkins
Date:
On Thu, Apr 21, 2005 at 09:51:16AM -0600, mmiranda@americatel.com.sv wrote:
> Hi people, is there a swtich or something to instruct a between statement
> hat it must perform a most specific match ?
> These are the details, i have these table
>
>  name        | start        | end
> ---------------+---------------+-----------------
> general     | 2660000  | 2669999
> specific     | 2660124  | 2660124
> (2 rows)
>
>
> unsing the query:  select name where '2660124' between start and end
>
> i got:
>
>
> name
> ---------
> general
> specific
>
>
> How can i get only specific?

....ORDER BY end-start ASC LIMIT1; ?

Cheers,
  Steve

Re: Most specific match using between

From
mmiranda@americatel.com.sv
Date:
On Thu, 2005-04-21 at 10:51, mmiranda@americatel.com.sv wrote:
> > These are the details, i have these table
> >
> >  name        | start        | end
> > ---------------+---------------+-----------------
> > general     | 2660000  | 2669999
> > specific     | 2660124  | 2660124
> > (2 rows)
> >
> >
> > unsing the query:  select name where '2660124' between start and end
> >
> > i got:
> >
> >
> > name
> > ---------
> > general
> > specific
> >
> >
> > How can i get only specific?
>
> Do you want the ONE row that's closest, or a set of rows that
> are fairly
> close?  Not sure what your specification it exactly, but how about:
>
> select name where '2660124' between start and end order by abs
> (start-end) limit 1;
>

I want the one row that is closest, your query seems to work...

---