开发者

Selecting two specific mysql table rows in a single query

开发者 https://www.devze.com 2022-12-30 11:01 出处:网络
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

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

0

精彩评论

暂无评论...
验证码 换一张
取 消