Mysql – Order by x DESC – but only continuous rows

MySQLorder-by

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?

Best Answer

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

Related Solutions

MySQL – How to Order Query by Specific Letter Using ORDER BY

Have a look at below example

DROP TABLE IF EXISTS products;

create table products(pname CHAR(30),pdescription CHAR(30),price DECIMAL(10,2),manufacturer CHAR(30));

INSERT INTO products VALUES
    ('Toys','These are toys',15.25,'ABC'),
    ('Dolls','These are Dolls',35.25,'PQR'),
    ('DustPan','These are DustPan',75.25,'AZD'),
    ('Doors','These are Doors',175.25,'RAZD'),
    ('TV','These are TV',11175.25,'RAZD'),
    ('Bed','These are Bed',1175.25,'ARAZD');

/** Check all data **/

SELECT * FROM products;
+---------+-------------------+----------+--------------+
| pname   | pdescription      | price    | manufacturer |
+---------+-------------------+----------+--------------+
| Toys    | These are toys    |    15.25 | ABC          |
| Dolls   | These are Dolls   |    35.25 | PQR          |
| DustPan | These are DustPan |    75.25 | AZD          |
| Doors   | These are Doors   |   175.25 | RAZD         |
| TV      | These are TV      | 11175.25 | RAZD         |
| Bed     | These are Bed     |  1175.25 | ARAZD        |
+---------+-------------------+----------+--------------+
6 rows in set (0.00 sec)

/** Order by D% **/
SELECT 
        pname, pdescription, price
    FROM
        products
    ORDER BY 
    CASE
        WHEN pname LIKE 'D%' THEN 1
        ELSE 2
    END;
+---------+-------------------+----------+
| pname   | pdescription      | price    |
+---------+-------------------+----------+
| Dolls   | These are Dolls   |    35.25 |
| DustPan | These are DustPan |    75.25 |
| Doors   | These are Doors   |   175.25 |
| Toys    | These are toys    |    15.25 |
| TV      | These are TV      | 11175.25 |
| Bed     | These are Bed     |  1175.25 |
+---------+-------------------+----------+
6 rows in set (0.00 sec)

Postgresql – How to order by Levenshtein distance

You didn't tell us what the "failed attempt" means. But something like this should work:

with ldist as (
   select name, 
          levenshtein(substring(name,1,200), lag(name) OVER (order by name))  as distance
   FROM books 
   WHERE name <> ''
) 
select * 
from ldist
order by distance;

you don't want partition by name because that essentially puts every name into a single group and thus there is no "previous" row to.

As you you want the "previous" row based on the ordering of the name column, you need an order by name in the window definition.

Best Answer

Output:

Related Solutions

MySQL – How to Order Query by Specific Letter Using ORDER BY

Postgresql – How to order by Levenshtein distance

Related Question