1

I have a table with an ID (integer) and a text field (the name). My current query looks like this:

SELECT id, name FROM table WHERE id <= X ORDER BY id DESC

X is an ID I get from another query. What this query currently does is it outputs all the entries lower than the ID I put into, ordered by ID.

What I'd like to achieve is that this is only done until the first "gap" in the IDs - for example, with a table like this:

id      name
1       Test 1
2       Test 2
3       Test 3
6       Test 6
7       Test 7
8       Test 8
9       Test 9

I'd like to have a query which only returns the following set for X = 8:

id      name
8       Test 8
7       Test 7
6       Test 6

Because then, there is a gap in the IDs (ID 5 is missing), and then I don't need the other lower IDs.

How could that be done?

5

This should work:

SELECT id, name 
FROM data
WHERE id <= 8 AND id >= (
    SELECT MAX(d1.id)
    FROM data d1
    LEFT JOIN data d2 ON d2.id = d1.id - 1
    WHERE d2.id IS NULL
)
ORDER BY id DESC
;

The subquery look for the first gap (i.e. 5 to 6). See SQL Fiddle.

Output:

id | name 
 8 | Test 8 
 7 | Test 7 
 6 | Test 6 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.