I have Price_trend table in that m_id and c_id are foreign keys. I need m_id column 3,7 top two records. Her is my db-fiddle

table: price_trend

My Attempt:

I have written the query up to this point, but am stuck on what to do next

select * from price_trend where c_id=1 and m_id in(3,7) limit 4;

I want result:

CodePudding user response：

On MySQL 8 we can use the ROW_NUMBER() window function:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY m_id ORDER BY date DESC) rn
    FROM price_trend
    WHERE c_id = 1 AND m_id IN (3, 7)
)

SELECT id, c_id, m_id, date
FROM cte
WHERE rn <= 2
ORDER BY m_id, date DESC;

ROW_NUMBER() assigns a sequence 1, 2, 3 to each record in a given m_id group, with the most recent record being assigned 1, the next most recent 2, and so on. In the outer query we then restrict to only records having the first two row numbers.

CodePudding user response：

In MySQL 5.X, you can use variables to generate the ranking index. Once you get it, you can select ranking values less or equal to 2.

SET @rownum = 0;
SET @prevmid = NULL;

SELECT id, c_id, m_id, date_
FROM (SELECT id, c_id, date_,
             IF(m_id = @prevmid, @rownum := @rownum   1, @rownum := 1) AS rn,
             IF(m_id = @prevmid, m_id, @prevmid := m_id) AS m_id
      FROM price_trend
      WHERE c_id = 1 AND m_id IN (3, 7)
      ORDER BY id) cte
WHERE rn <= 2

Check the demo here.