Selecting two specific mysql table rows in a single query
Lets say I have a table with 2开发者_StackOverflow0 entries. They are sorted by date (date is a column name >_>) in descending order. How would I go about selecting ONLY the newest entry and the 15th oldest entry?
I am getting all 15 results by doing the following query
SELECT * FROM mytable m WHERE col1 = "zzz" ORDER BY date DESC LIMIT 15;
Use:
SELECT x.*
FROM (SELECT a.*,
@rownum := @rownum + 1 AS rank
FROM mytable a
JOIN (SELECT @rownum := 0) r
WHERE a.col1 = "zzz"
ORDER BY a.date DESC) x
WHERE x.rank IN (1, 15)
you may need to use UNION of two SELECTs
(SELECT * FROM mytable m WHERE col1 = "zzz" ORDER BY date LIMIT 1, 15)
UNION
(SELECT * FROM mytable m WHERE col1 = "zzz" ORDER BY date DESC LIMIT 1)
UPDATE:
added parenthesis
精彩评论