Re: a select query problem

Thanks, but decode doesn't seem to apply. Decoding the sign of the expression will not result in a single-row result based on the minimum positive value of the expression (x - seq_no).

If you think about the problem generally, it corresponds to sorting rows of a table using some given comparison function applied to a column, and then selecting the row corresponding to the minimum (or maxium) value of the comparison function.

"cf" <news_at_tolede.com> wrote in message
news:4090ac4d$0$19646$4d4eb98e_at_read.news.fr.uu.net...
> 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 - 16:30:38 CDT