Order by x DESC - but only continuous rows 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 &lt;= 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?
This should work:

SELECT id, name 
FROM data
WHERE id &lt;= 8 AND id &gt;= (
    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