Re: a select query problem

select event_id,
 seq_no ,
 decode(sign(11 - seq_no ),-1,0,12 - seq_no )   from Events

Christophe.

"Ralf Fernan" <ralphfernan.N0SPAM_at_yahoo.com> a écrit dans le message de news:zMXjc.61156$um3.1172549_at_bgtnsc04-news.ops.worldnet.att.net...
> Select query problem:
> A simple table 'Events' has two columns, event id and sequence number. I
> wish to select that row which has the minimum non-negative value of (x -
> seq_no), where x is some user-defined value. (Less precisely, I want to
> select the event with the sequence that is closest to x.) I used an inner
> join to get the row, but was wondering if there is a more straightforward
> solution.
>
> Thanks!
>
> *****************************************************************
>
> Events
> ----------------------
> event_id VARCHAR2(3)
> seq_no NUMBER
> ----------------------
>
> Example:
> event_id seq_no (12 - seq_no)
> --------------------------------------
> A 11 1
> B 9 3
> C 15 -3
> D 13 -1
>
> suppose x = 12, then I expect the query to return event_id A (since 12-11
=
> 1, and 1 >= 0).
>
> I solved this using an inner join:
>
> SELECT t1.event_id, t1.seq_no FROM Events t1
>
> WHERE (12-t1.seq_no) =
>
> (SELECT min(12-t2.seq_no) FROM Events t2
> WHERE (12-t2.seq_no) >= 0);
>
>
Received on Thu Apr 29 2004 - 02:18:38 CDT