CODEWITHSUNDEEP
sqllimit
user2154768
user2154768

Reputation: 1130

SQL LIMIT keyword: Ensure a specific result is included

I need to write an SQL prefetcher along the lines of

SELECT * WHERE id == x OR id >= y LIMIT 100 FROM ...

where I need the result id == x to be included, while the other results are just bonus. Is this possible? If not, I am thinking if the ids are sorted, and the returned results are in their ordering, I can still make the query in a way where it is guaranteed to include id == x. But could be tricky.

Edit: So the answer(from rbm) is this:

(SELECT * WHERE id == x)
UNION
(SELECT * WHERE id > y LIMIT 100)

Note: It's ok that I may have +-1 number of results

Upvotes: 0

Views: 68

Answers (2)

Lostblue
Lostblue

Reputation: 419

After putting it to SQL syntax, it would look like :

SELECT *
FROM ...
WHERE id = x OR id >= y
ORDER BY CASE WHEN id = x THEN 0 ELSE 1 END ASC
LIMIT 100

EDIT : Reminder: Limit is applied after order.

Upvotes: 1

Dan Bracuk
Dan Bracuk

Reputation: 20804

If you need id = x to be included, do something like this:

select case when id = x then 1 else 2 end sortOrder
etc
order by sortOrder

Upvotes: 2

Related Questions