CODEWITHSUNDEEP
sqlsql-server
intrigued_66
intrigued_66

Reputation: 17248

Multiple rows match, but I only want one?

Sometimes I wish to perform a join whereby I take the largest value of one column. Doing this I have to perform a max() and a groupby- which prevents me from retrieving the other columns from the row which was the max (beause they were not contained in a GROUP BY or aggregate function).

To fix this, I join the max value back on the original data source, to get the other columns. However, my problem is that this sometimes returns more than one row.

So, so far I have something like:

SELECT * FROM
    (SELECT Col1, Max(Col2) FROM Table GROUP BY Col1) tab1
JOIN
    (SELECT Col1, Col2 FROM Table) tab2
ON tab1.Col2 = tab2.Col2

If the above query now returns three rows (which match the largest value for column2) I have a bit of a headache.

If there was an extra column- col3 and for the rows returned by the above query, I only wanted to return the one which was, say the minimum Col3 value- how would I do this?

Upvotes: 0

Views: 3015

Answers (3)

Arion
Arion

Reputation: 31239

If you are using SQL Server 2005+. Then you can do it like this:

CTE way

;WITH CTE
AS
(
    SELECT
        ROW_NUMBER() OVER(PARTITION BY Col1 ORDER BY Col2 DESC) AS RowNbr,
        table.*
    FROM
        table
)
SELECT
    *
FROM
    CTE
WHERE
    CTE.RowNbr=1

Subquery way

SELECT
    *
FROM
    (
    SELECT
        ROW_NUMBER() OVER(PARTITION BY Col1 ORDER BY Col2 DESC) AS RowNbr,
        table.*
    FROM
        table
    ) AS T
WHERE
    T.RowNbr=1

Upvotes: 4

GarethD
GarethD

Reputation: 69769

Assuming you are using SQL-Server 2005 or later You can make use of Window functions here. I have chosen ROW_NUMBER() but it is not hte only option.

;WITH T AS
(   SELECT  *,
            ROW_NUMBER() OVER(PARTITION BY Col1 ORDER BY Col2 DESC) [RowNumber]
    FROM    Table
)
SELECT  *
FROM    T
WHERE   RowNumber = 1

The PARTITION BY within the OVER clause is equivalent to your group by in your subquery, then your ORDER BY determines the order in which to start numbering the rows. In this case Col2 DESC to start with the highest value of col2 (Equivalent to your MAX statement).

Upvotes: 1

levi
levi

Reputation: 3511

As I got it can be something like this

SELECT * FROM
    (SELECT Col1, Max(Col2) FROM Table GROUP BY Col1) tab1
JOIN
    (SELECT Col1, Col2 FROM Table) tab2
ON tab1.Col2 = tab2.Col2 and Col3 = (select min(Col3) from table )

Upvotes: 1

Related Questions