Emulating PARTITION OVER with MySQL 5.7

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.

Solution

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