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 (
               ROW_NUMBER() OVER(PARTITION BY p.customer 
                                     ORDER BY 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(,  -- change to MAX if you want the highest
        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 =
    GROUP BY x.customer,
    Answered on February 9, 2019.
    Add Comment

  • Your Answer

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