How to retrieve the rows (with maximum value in a field) having a another common field ?

Question

I have a table; let it be called table1; with the following fields and data

I need a query that returns the record with the maximum value in Field3 for each group of records having the same value in Field2. So that the query returns:

How could this be done using SQL queries ?

Answer 1

This:

WITH    q AS
        (
        SELECT  *, ROW_NUMBER() OVER (PARTITION BY field2 ORDER BY field3 DESC) AS rn
        FROM    table1
        )
SELECT  *
FROM    q
WHERE   rn = 1

or this:

SELECT  q.*
FROM    (
        SELECT  DISTINCT field2
        FROM    table1
        ) qo
CROSS APPLY
        (
        SELECT  TOP 1 *
        FROM    table1 t
        WHERE   t.field2 = qo.field2
        ORDER BY
                t.field3 DESC
        ) q

Depending on the field2 cardinality, the first or the second query can be more efficient.

See this article for more details:

• SQL Server: Selecting records holding group-wise maximum

Answer 2

My go:

SELECT t1.Field1
, t1.Field2
, MAX(t1.Field3)
, t1.Field4
FROM table1 t1
GROUP BY t1.Field2