I have a table in MySql 5.7, where the names are repeated according to the date. I need to recover the first 2 records for each name.
example:
name year month
xxxx 2019 8
xxxx 2019 7
xxxx 2019 6
xxxx 2019 5
.... .... ..
zzzz 2019 5
zzzz 2019 4
zzzz 2019 3
zzzz 2019 2
.... .... ..
expected result:
name year month
xxxx 2019 8
xxxx 2019 7
zzzz 2019 5
zzzz 2019 4
other ...
I need to retrieve the first two records for each name, it is not valid to do so with date clauses.
the query I try to emulate:
SELECT
name, year, month
FROM (
SELECT
*,
row_number() OVER (PARTITION BY name ORDER BY year DESC, month DESC)
FROM
table
) a
WHERE row_number <= 2
thanks.
Before MySQL 8.0 you can't use window functions like ROW_NUMBER
. But you can use user-defined variables instead to emulate the ROW_NUMBER
function:
SELECT name, year, month FROM (
SELECT *, IF(@prev <> name, @rn:=0,@rn), @prev:=name, @rn:=@rn+1 AS rn
FROM example, (SELECT @rn:=0) rn, (SELECT @prev:='') prev
ORDER BY name ASC, year DESC, month DESC
) t WHERE rn <= 2;
Since MySQL 8.0 this query is much easier, using the ROW_NUMBER
function:
SELECT name, year, month FROM (
SELECT name, year, month,
ROW_NUMBER() OVER (PARTITION BY name ORDER BY year DESC, month DESC) AS rn
FROM example
) t WHERE rn <= 2;
demo on dbfiddle.uk
What if your partition by has two columns?
Using MySQL 5.7 with used-defined variables (and without ROW_NUMBER
):
-- using two columns on the partition (name and year)
SELECT name, year, month FROM (
SELECT *, IF(@prev <> name + year, @rn:=0,@rn), @prev:=name + year, @rn:=@rn+1 AS rn
FROM example, (SELECT @rn:=0)rn, (SELECT @prev:='')prev
ORDER BY name ASC, year DESC, month DESC
)t WHERE rn <= 2;
Using MySQL 8.0 with ROW_NUMBER
:
-- using two columns on the partition (name and year)
SELECT name, year, month FROM (
SELECT name, year, month, ROW_NUMBER() OVER (PARTITION BY name, year ORDER BY year DESC, month DESC) AS rn
FROM example
)t WHERE rn <= 2;
demo on dbfiddle.uk
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With