Most specific match using between

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;

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...

---

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