How to select all records from table apart from th

Question

I have a database that stores customers records and I would like to set up a cron job to overwrite these records periodically. I would like to say Select * from ORDERS where ORDER_ID is not in the top 100 of the list. Each row has its own order_id with the latest order_id being the latest order. I need to keep the latest 100 order ids in case of some problems. Thank you for your time.

Answer 1

You can left join a rowset of 100 last order_id's - this will result in all but 100 last having NULL in the left joined set.

SELECT o.* from `order-table` o
LEFT JOIN
  ( SELECT order_id FROM `order-table` ORDER BY order_id DESC LIMIT 100 ) o100
ON o.order_id = o100.order_id
WHERE o100.order_id IS NULL

Answer 2

select *
from Orders
where OrderID not in (
    select OrderID 
    from Orders 
    order by OrderID desc 
    limit 100)