Not getting this SQL query-CodePudding

Print all details of the 16th order placed by each customer if any.

How to print exact 16th Order?

SELECT COUNT(orderId) 
FROM orders
GROUP BY CustomerID
ORDER BY CustomerID;

CodePudding user response：

We can use a CTE and RANK to create a list of all orderId's, customerID's and their "order" as you named it. Then we fetch those entries from the entire result whose order is 16.

WITH result AS
(
  SELECT orderId, customerID, 
  RANK() OVER
    (PARTITION BY customerID 
     ORDER BY orderId) AS rnk 
  FROM orders
)
SELECT orderId, customerID 
FROM result 
WHERE rnk=16 
GROUP BY orderId, customerID
ORDER BY customerID;

For customerID's having less than 16 orders, nothing will be selected.

We can also use ROW_NUMBER instead of RANK in the above query, this makes no difference in your use case.

CodePudding user response：

Select * from 
(
  SELECT *, 
  DENSE_RANK() 
  OVER(
    PARTITION BY customerID 
    ORDER BY orderID
  ) my_rank 
  FROM orders
) as myTable 
where my_rank = 16 
order by CustomerID;

CodePudding user response：

You can just use offset like:

SELECT *
FROM orders
GROUP BY CustomerID
ORDER BY CustomerID
LIMIT 1 OFFSET 15;

and set the OFFSET value to 15 so it skips the first 15 values and prints from the 16th value and limit it to only one row by setting the LIMIT value to 1