SELECT *在MAX和第二个MAX id之间的列WHERE另一列等于某个输入

Question

I have a table called orders with (a.o.) columns sid and q1_requested_by. The sid column contains a unique id number, incrementing with every new input.

Every now and then there is an input in column q1_requested_by with the value ORDERS PROCESSED.

I want to select everything from the table which falls in between the latest ÒRDERS PROCESSED input and the latest but one ORDERS PROCESSED input.

I've tried the following, which doesn't work. It selects nothing.

SELECT * FROM orders 
WHERE sid < (SELECT max(sid) FROM orders
             WHERE q1_requested_by = 'ORDERS PROCESSED')
  AND sid > (SELECT TOP 2 sid FROM orders
             WHERE q1_requested_by = 'ORDERS PROCESSED'
             ORDER BY sid DESC)
ORDER BY sid DESC

How could I solve this?

Answer 1

First, you would use MySQL constructs:

SELECT o.*
FROM orders o
WHERE o.sid < (SELECT max(sid)
               FROM orders
               WHERE q1_requested_by = 'ORDERS PROCESSED'
              ) AND
      o.sid > (SELECT sid
               FROM orders
               WHERE q1_requested_by = 'ORDERS PROCESSED'
               ORDER BY sid DESC
               LIMIT 1, 1
              )
ORDER BY o.sid DESC