Select first row in each GROUP BY group?

Select first row in each GROUP BY group?

Asked on December 24, 2018 in PostgreSQL.
Add Comment


  • 1 Answer(s)

    On Oracle 9.2+ (not 8i+ as originally stated), SQL Server 2005+, PostgreSQL 8.4+, DB2, Firebird 3.0+, Teradata, Sybase, Vertica:

    WITH summary AS (
        SELECT p.id, 
               p.customer, 
               p.total, 
               ROW_NUMBER() OVER(PARTITION BY p.customer 
                                     ORDER BY p.total DESC) AS rk
          FROM PURCHASES p)
    SELECT s.*
      FROM summary s
     WHERE s.rk = 1

    Supported by any database:

    But you need to add logic to break ties:

      SELECT MIN(x.id),  -- change to MAX if you want the highest
             x.customer, 
             x.total
        FROM PURCHASES x
        JOIN (SELECT p.customer,
                     MAX(total) AS max_total
                FROM PURCHASES p
            GROUP BY p.customer) y ON y.customer = x.customer
                                  AND y.max_total = x.total
    GROUP BY x.customer, x.total
    Answered on February 9, 2019.
    Add Comment


  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.