a select query problem

From: Ralf Fernan <ralphfernan.N0SPAM_at_yahoo.com>
Date: Thu, 29 Apr 2004 00:13:19 GMT
Message-ID: <zMXjc.61156$um3.1172549@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 Wed Apr 28 2004 - 19:13:19 CDT