CODEWITHSUNDEEP
sqlsql-server-2008group-bymaxwhere-clause
Reeya Oberoi
Reeya Oberoi

Reputation: 861

How to return results with MAX value for column A by grouping on column B and where filter on Column C

Pardon the title but this is what I have in table t1.

Data Layout

I want to return the rows for the same TRX_KEY and ID = 1 only when AMOUNT is MAX within the group. So in this case, I want following result -

RESULT

Here is the sample data -

create table t1( TRX_KEY int not null ,ID int null, AMOUNT int null)
insert into t1 values (1,1,1000)
insert into t1 values (1,2,100)
insert into t1 values (1,3,10)

insert into t1 values (2,1,100)
insert into t1 values (2,2,1000)
insert into t1 values (2,3,10)

insert into t1 values (3,1,5000)
insert into t1 values (3,2,1000)
insert into t1 values (3,3,10)

Upvotes: 1

Views: 28

Answers (1)

Code Different
Code Different

Reputation: 93181

Use a window function like ROW_NUMBER:

;WITH temp AS (
    SELECT TRX_KEY, ID, Amount,
            ROW_NUMBER() OVER (PARTITION BY TRX_KEY ORDER BY Amount DESC) As RowNumber
    FROM    #t1
)

SELECT  *
FROM    temp
WHERE   RowNumber = 1 AND ID = 1

Upvotes: 3

Related Questions