skip to Main Content

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;

3

Answers


  1. Chosen as BEST ANSWER
    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;
    

  2. 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.

    Login or Signup to reply.
  3. 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

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search