CODEWITHSUNDEEP
mysqlsql
user0000001
user0000001

Reputation: 2233

MySQL: Select last X rows and then specific rows afterwards

He is a tricky query that I can't seem to figure out unless I use PHP - which I want to avoid at any cost. So here is what I am trying to do...

I have a table named invoices. This table has a field called paid. This field is ticked after an invoice has been considered paid. Now, I have a page called Latest Invoices that shows a giant list of the first 100 invoices (paid & unpaid).

SELECT * FROM invoices ORDER BY id DESC, id LIMIT 100

Obviously works great. However, I want to adjust this statement by adding a condition that will also include any invoices that ARE NOT paid following the last 100 invoices.

So in other words:
- The first 100 invoices will show PAID/UNPAID
- And then any unpaid invoices will be afterwards (no limit)

Cheers

Upvotes: 0

Views: 50

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269483

You need to identify the first 100 invoices. Here is a way of doing that by using a join:

SELECT i.*
FROM invoices i left outer join
     (select i.*
      from invoices i
      order by id desc
      limit 100
     ) i100
     on i.id = i100.id
WHERE i100.id is not null or i.paid = 0
ORDER BY id desc;

The order by just uses id. This will put the first 100 at the top. The rest of the not-paid will follow those in inverse id order.

Upvotes: 3

Related Questions