Have Geek Brain

How do I get rows (with the maximum value in a field) that have another common field?

I have a table; let it be called table1

; with the following fields and data

alt text http://img228.imageshack.us/img228/3827/45939084.png

I need a query that returns the record with the maximum value in Field3

for each group of records that have the same value in Field2

. To make the request return:

alt text http://img87.imageshack.us/img87/62/48847706.png

How can this be done using SQL queries?

+2


a source to share


1 answer


It:

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 that:

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 capacity, the field2

first or second request may be more efficient.

See this article for more details:

+5


a source






More articles:


All Articles