mark mark - 1 month ago 11
MySQL Question

Select N items directly before a given ID

If I have a row for each of the alphanumerically ordered items (ids) below:

aa ab ac ba cc cf ff gh h4 ia


I would like to select the 3 items directly prior to
cc
, which would be
ab
,
ac
and
ba
(in that order). My MySQL query does not pick the items directly prior to
cc
, but rather from the beginning of the list.

SELECT * FROM things WHERE id < 'cc' ORDER BY id LIMIT 3.


Again, this query does not work because it does not retrieve the items directly before
cc
. What is the correct approach here?

Answer

You are very close:

SELECT *
FROM things
WHERE id < 'cc'
ORDER BY id DESC
------------^
LIMIT 3;

You need to sort the items in descending order to get the "biggest" ones before 'cc'.

Also, for three items you want limit 3. I assume the "2" is a typo.

If you then want these in alphabetical order, use a subquery and order again:

SELECT t.*
FROM (SELECT t.*
      FROM things t
      WHERE id < 'cc'
      ORDER BY id DESC
      LIMIT 3
     ) t
ORDER BY id ASC;