CODEWITHSUNDEEP
sqlsql-server
Richard Todd
Richard Todd

Reputation: 2496

Max value grouped by two fields

My problem is to select the maximum value from a tabel and group by two fields. The table is very large, say 5billion records and the data is arranged like this:

LocationNumber | Event | Value
             1 |     1 |    10
             1 |     2 |    20
             1 |     3 |    30
             2 |     1 |    20
             2 |     2 |    50

I'd like to have the output as

LocationNumber | Event | Value
             1 |     3 |    30
             2 |     2 |    50

Any ideas on this?

Upvotes: 1

Views: 69

Answers (4)

Vineeth Vijayan
Vineeth Vijayan

Reputation: 1329

select LocationNumber, MAX(event), MAX(Val) from #tbl group by LocationNumber

this will work, But I am concerned about 5 billion records. Will it perform effectively ?

Upvotes: 0

TechDo
TechDo

Reputation: 18659

Please try:

select
    LocationNumber,
    Event,
    Value
from(
    select
        LocationNumber,
        Event,
        Value,
        ROW_NUMBER() over (Partition by LocationNumber order by Event desc, Value desc) RNum
    From YourTable
)x where RNum=1

Upvotes: 0

sunysen
sunysen

Reputation: 2361

try

SELECT LocationNumber, MAX(Event), Max(value)
FROM tablename
GROUP BY LocationNumber

Upvotes: 0

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79979

Just GROUP BY LocationNumber and use MAX like this:

SELECT LocationNumber, MAX(Event), Max(value)
FROM tablename
GROUP BY LocationNumber

Upvotes: 4

Related Questions