Interesting Limits

[Inferno_str1ke]

I have a table that contains items with expiry dates on each item. I currently have a query that only shows unexpired items.
SELECT * FROM items WHERE expiry>$date
I need to change this so that it shows the most recent three expired items as well as the unexpired items. I could do it like this:
SELECT * FROM items WHERE expiry>$date;
SELECT * FROM items WHERE expiry=<$date ORDER BY expiry DESC LIMIT 3

But this relies on my using two queries. I could also just get the entire table, or estimate a date so that I'd have at least three expired items, and just filter out the rest with PHP. I'm just wondering if there's a more elegant single solution to this, that would allow one query to get me all useful records?

[svidgen]

Off the top of my head, I think you're stuck with a UNION:
SELECT * FROM items WHERE expiry>$date
UNION
SELECT * FROM items WHERE expiry=<$date ORDER BY expiry DESC LIMIT 3;
The database will still have to run both queries, of course. But, you'll be saving yourself some of the overhead associated with making multiple calls. In any case, as long as expiry is indexed, these will both be relatively cheap calls. My only concern is the lack of a LIMIT clause on the first query ... Are you limiting or paginating your results in the application?

[Shorts]

You could use a UNION:


(SELECT * FROM items WHERE expiry>$date) UNION (SELECT * FROM items WHERE expiry=<$date ORDER BY expiry DESC LIMIT 3)


Edit: Damnit... beat to the punch... But yeah, union. Also, besides the limiting issue that Jon brought up, also would suggest adding a field to the second select:
SELECT *,1 AS expired FROM ...
so you have an array, expired that is 1 (and can add SELECT *,0 AS expired FROM ... for the first, so your while already knows if it's expired through a 1/0)...