How to limit to maximum count of rows in single table in MySQL 5.5-CodePudding

I have a single table, with the following columns:

Id BigInt
CustomerId BigInt
Order varchar(50)
DateOfOrder date

What I try to achieve is the following: Getting all CustomerId, which have a specific string for the column order and the DateOfOrder is since 2018 and from this only the last two orders.

I started with the following SQL-Statement

Select o.CustomerId as CustomerId, o.Id as Id 
  from order o 
 where o.Order="Merchandise" 
   and year(o.DateOfOrder)>= 2018 
 order by o.DateOfOrder desc;

But how do I get only the 2 top orders of each CustomerId?

THX a lot in advance

CodePudding user response：

what are you looking for is called row_number and you should use it with Subquery in order to get 2 top orders of each CustomerId based on DateOfOrder

 SELECT 
  * 
FROM 
  (
    SELECT 
      o.customerid AS customerid, 
      o.id AS id,
       row_number() over (
        partition by o.customerid 
        ORDER BY  o.DateOfOrder  DESC
      --ORDER BY  o.Order   DESC
      ) rn 
    FROM 
      ORDER o 
    WHERE 
      o.ORDER = "Merchandise" 
      AND year(o.dateoforder)>= 2018
  ) 
WHERE 
  rn <= 2

for lower versions of Mysql that does not support row_number use correlated-subquery

 SELECT o1.id ,
       o1.customerid ,
       o1.order ,
       o1.dateoforder
FROM   order O1
WHERE  2 >
       (
              SELECT Count(*)
              FROM   order O2
              WHERE  o1.customerid = o2.customerid
              AND    o1.order > o2.order )
AND
o1.ORDER="Merchandise"
AND    year(o1.dateoforder)>= 2018

CodePudding user response：

I think, I solved my problem. Not beatuiful, but working. If someone is interested in the solution: First I get all oldest Orders into a Temporary Table. Then I get second oldest orders, by using the id of the oldest orders, which are not allowed to be in the query The result is also stored in a temporary table.. And then, I union the results of the temporary tables to get my data, which I wanted to have.

Create Temporary Table Top_Orders 
    Select o.Id Id, o.CustomerId CustomerId, Max(o.DateOfOrder) OrderDate from order o where o.Order = "Merchandise" and year(o.DateOfOrder) >= 2018 group by CustomerId;
    
Create Temporary Table Top_Orders2
    Select o.Id Id, o.CustomerId CustomerId, Max(o.DateOfOrder) OrderDate from order o where o.Order = "Merchandise" and year(o.DateOfOrder) >= 2018 and o.Id not in (Select Id from Top_Orders) group by CustomerId;
    
Select o1.CustomerID, o1.Id from order o1 where o1.Id in (Select t1.Id from Top_Orders t1) union Select o2.CustomerId, o2.Id from order o2 where o2.Id in (Select t2.Id from Top_Orders2 t2);
    
Drop Temporary Table Top_Orders;
Drop Temporary Table Top_Orders2;