Re: a select query problem

Hello Ralf,

Pls. see below:

"Ralf Fernan" <ralphfernan.N0SPAM_at_yahoo.com> wrote in message news:zMXjc.61156$um3.1172549_at_bgtnsc04-news.ops.worldnet.att.net...
>I used an inner
> join to get the row, but was wondering if there is a more straightforward

Firstly, I should point out that your solution ain't no join.

> 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);
>

As to an alternative solution requiring only one FTS, you can do this:

select * from (select * from Events where (12 - seq_no) >=0 order by (12 - seq_no))
where rownum=1

VC Received on Thu Apr 29 2004 - 17:28:18 CDT