Question 21426 in Mysql Question posted in
The official MySQL documentation can be found here.

finding top two rows using LIMIT – Mysql

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

id c_id m_id date
1 1 3 2022-12-08
2 1 3 2022-12-06
3 1 3 2022-12-05
4 1 7 2022-12-03
5 1 7 2022-12-02
6 1 7 2022-12-01

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:

id c_id m_id date
1 1 3 2022-12-08
2 1 3 2022-12-06
4 1 7 2022-12-03
5 1 7 2022-12-02
Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search