How to select first row in each group using GROUP BY ?

 While grouping the rows on a certain criteria using GroupBy, instead of retrieving each and every row in a particular group, the requirement sometimes is to select only single row from each of the group.

In this scenario the probable solution could be use the ranking function under a CTE and then only select those records for which rank is 1. 

For example from purchases table the information required is the highest purchase amount of each customer. As there might be multiple purchases for a customer but only single row needs to be fetched. 

WITH CTE AS (
    SELECT p.customer_id, 
           p.customer, 
           p.total, 
           ROW_NUMBER() OVER(PARTITION BY p.customer 
                                 ORDER BY p.total DESC) AS rank
      FROM PURCHASES p)
 SELECT *
   FROM CTE
 WHERE rank = 1

If CTE is not supported by Database

SELECT a.Customer_id,  
         a.customer, 
         a.total
    FROM PURCHASES a
    JOIN (SELECT p.customer,
                 MAX(total) AS max_total
            FROM PURCHASES p
        GROUP BY p.customer) y ON y.customer = a.customer
                              AND y.max_total = a.total

Comments

Popular Posts