Re: [GENERAL] Most specific match using between

[mmiranda]

On Thu, 2005-04-21 at 10:51, [EMAIL PROTECTED] wrote:
> > These are the details, i have these table
> > 
> >  name| start| end 
> > ---+---+-
> > general | 266  | 266
> > 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...

---

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] Most specific match using between

[Steve Atkins]

On Thu, Apr 21, 2005 at 09:51:16AM -0600, [EMAIL PROTECTED] 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 | 266  | 266
> 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

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [GENERAL] Most specific match using between

[Scott Marlowe]

On Thu, 2005-04-21 at 10:51, [EMAIL PROTECTED] 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 | 266  | 266
> 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;

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq