CODEWITHSUNDEEP
mysqlsql-order-by
Petr Hejda
Petr Hejda

Reputation: 43571

MySQL filter on the same colum as is ordered by

Desired output:

I'd like to get 2 rows dated 2019-11-23 17:25:00 and earlier in ascending order, so I wouldn't have to sort them on the application layer.

|2019-11-23 17:24:00|
|2019-11-23 17:25:00|

My table: (I left out unnecessary data for purposes of this question.)

+===================+
|starttime          |
+===================+
|2019-11-23 17:22:00|
|2019-11-23 17:23:00|
|2019-11-23 17:24:00|
|2019-11-23 17:25:00|
|2019-11-23 17:26:00|
|2019-11-23 17:27:00|
+===================+

What I've tried so far:

1) This query returns the first 2 items (incorrect):

SELECT `starttime`
FROM `table`
WHERE `starttime` <= "2019-11-23 17:25:00"
ORDER BY `starttime` ASC
LIMIT 2

returned data:

|2019-11-23 17:22:00|
|2019-11-23 17:23:00|

2) This query returns the correct 2 items but in opposite order:

SELECT `starttime`
FROM `table`
WHERE `starttime` <= "2019-11-23 17:25:00"
ORDER BY `starttime` DESC
LIMIT 2

returned data

|2019-11-23 17:25:00|
|2019-11-23 17:24:00|

Upvotes: 0

Views: 22

Answers (1)

forpas
forpas

Reputation: 164174

Your 2nd query is correct and all you have to do is nest it inside another where you will reorder the 2 rows:

SELECT t.*
FROM (
  SELECT `starttime`
  FROM `table`
  WHERE `starttime` <= "2019-11-23 17:25:00"
  ORDER BY `starttime` DESC
  LIMIT 2
) t
ORDER BY t.`starttime`

See the demo.

Upvotes: 1

Related Questions