CODEWITHSUNDEEP
sqlsqliteselect-query
Zaur Nasibov
Zaur Nasibov

Reputation: 22659

SQL query with additional 'nearest' row

Consider the following table structure: 

id   speed 
 1   100
 2   200
 3   300
 4   400
 5   500

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.

Upvotes: 1

Views: 80

Answers (3)

Ruslan Veselov
Ruslan Veselov

Reputation: 337

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.

Upvotes: 3

juergen d
juergen d

Reputation: 204766

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

Upvotes: 1

Joseph B
Joseph B

Reputation: 5669

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;

Upvotes: 2

Related Questions