SQL query with additional 'nearest' row

Question

Consider the following table structure:

Consider the following query: "SELECT * FROM records WHERE speed >= 300" - this will return the rows #3, 4, 5. Is there a way to modify this query so that with the same 300 speed parameter it would also return the first row that does not fit the condition i.e. #2. So that the end results would be rows #2, 3, 4, 5?

UPD: Note that all the values and the records count here are arbitrary and for example only. The database is SQLite.

What version of SQL? Sql Server? MySQL? Etc. – Siyual Aug 21 '14 at 14:26 — Siyual, Aug 21 '14 at 14:26

Ruslan Veselov · Accepted Answer · 2015-08-26T16:24:29.250

3

Try this:

SELECT *
FROM Test
WHERE Speed >= 300

UNION

SELECT * 
FROM (
    SELECT *
    FROM Test
    WHERE Speed < 300
    ORDER BY Speed DESC
    LIMIT 1
) AS XXX

ORDER BY Speed

See DEMO

Note: changed for SQLite syntax.

edited Aug 26 '15 at 16:24

answered Aug 21 '14 at 14:43

Ruslan Veselov

337
1
10

I agree with the idea to just union the one row that is nearest, but this is `SQL Server` syntax and the question is tagged `SQLite` – DrCopyPaste Aug 21 '14 at 14:47
@DrCopyPaste Yeah, sorry for that. Should replace `TOP` with `LIMIT`, but I can not check, cause http://sqlfiddle.com/ is not available. – Ruslan Veselov Aug 21 '14 at 15:00

score 2 · Answer 2 · answered Aug 21 '14 at 14:55

2

Try this simple query, which selects the row with the maximum id and speed < 300 as well as rows with speed >= 300.

SELECT * 
FROM records 
WHERE speed >= 300
OR id = (SELECT MAX(id) FROM records WHERE speed < 300)
ORDER BY id;

answered Aug 21 '14 at 14:55

Joseph B

5,519
1
15
19

This is a little bit wrong in case when ID is in other order than Speed, i.e.: ID 1 - Speed 200 and ID 2 - Speed 100. – Ruslan Veselov Aug 21 '14 at 23:09

juergen d · Answer 3 · 2014-08-21T15:00:55.450

1

select * 
from records
order by abs(300 - speed) asc
limit 5

edited Aug 21 '14 at 15:00

answered Aug 21 '14 at 14:28

juergen d

201,996
37
293
362

What if you have no idea, how many rows are there? Meaning that '5' is not a constant, but a variable? – Zaur Nasibov Aug 21 '14 at 14:34
IIRC `TOP` wont work with `SQLITE` it should rather be `LIMIT 5` at the end, shouldn't it? – DrCopyPaste Aug 21 '14 at 14:44
@DrCopyPaste: Absolutely. I was under the implession it was SQLServer. – juergen d Aug 21 '14 at 15:01

SQL query with additional 'nearest' row

3 Answers3