Selecting entries with nearest value less than given value

Question

I want to select the entries with the nearest value less than a given value with SQL (No PL/SQL! or such thing).

I got this far:

select max(RUN_ID) from RUN_TABLE where KEY = 'TEST#33' and RUN_ID < 3

This returns the row with the highest value less than 3 matching the key but I want to be able to select all columns.

Run_ID  Entity  Key
1         HK    TEST#11
2         AB    TEST#22
2         CK    TEST#33
3         TB    TEST#22
3         DB    TEST#33

I would like to be able when having the key TEST#22 and maximal RUN_ID 4 to select the row:

3   TB   TEST#22

And when saying maximal RUN_ID 2 to retrieve

2   AB   TEST#22

Which database engine are you using? (ie mysql, sql server, postgres, oracle?) — Bohemian, Jun 03 '16 at 16:27

Bohemian · Accepted Answer · 2016-06-03T17:08:49.107

2

Order by run id descending and take only the first row, for Oracle:

select top 1 * from RUN_TABLE
where KEY = 'TEST#33' and RUN_ID < 3
order by RUN_ID desc
fetch first 1 rows

Or SQL Server (because SQLFiddle's Oracle option is down):

select top 1 * from RUN_TABLE
where KEY = 'TEST#33' and RUN_ID < 3
order by RUN_ID desc

with live demo on SQLFiddle.

edited Jun 03 '16 at 17:08

answered Jun 03 '16 at 16:35

Bohemian

412,405
93
575
722

Thanks. Great idea. For Oracle 11g you can use rownum = 1 by the way. – MMM Jun 03 '16 at 16:44
@Puddle "11g" is an Oracle verison, not an SqlServer version. Which are you using, Oracle or SqlServer? You comment above says SqlServer, but your version says Oracle. – Bohemian Jun 03 '16 at 16:48
Oh yes, I am using Oracle – MMM Jun 03 '16 at 17:06

Stavr00 · Answer 2 · 2016-06-03T17:13:31.837

0

select Run_ID, Entity, Key from from RUN_TABLE a
where Run_ID = (select max(RUN_ID) from RUN_TABLE b where b.Key = a.Key and RUN_ID < 3)
and KEY = 'TEST#33'

edited Jun 03 '16 at 17:13

answered Jun 03 '16 at 15:37

Stavr00

3,219
1
16
28

Sounds weird but do you have an idea how it can be done without writing 'TEST#33' twice? – MMM Jun 03 '16 at 15:49

score 0 · Answer 3 · answered Jun 03 '16 at 15:41

0

Something like:

select t1.*
from RUN_TABLE t1
join (
   select max(RUN_ID) AS MAX_RUN_ID
   from RUN_TABLE 
   where KEY = 'TEST#33' and RUN_ID < 3
) t2 ON t1.RUN_ID = t2.MAX_RUN_ID
WHERE KEY = 'TEST#33'

answered Jun 03 '16 at 15:41

Giorgos Betsos

71,379
9
63
98

Sounds weird but do you have an idea how it can be done without writing 'TEST#33' twice? – MMM Jun 03 '16 at 15:49

Selecting entries with nearest value less than given value

3 Answers3