CODEWITHSUNDEEP
sql
chillydk147
chillydk147

Reputation: 161

SQL selecting specific DISTINCT rows

I'm trying to extract some data were the Event column is distinct but I specifically want only rows that contain the highest number from the Value column, I'm thinking it's some mis-mash of using DISTINCT and GROUP BY but my sql knowledge is limited at the moment, any help would be great

enter image description here

Upvotes: 0

Views: 53

Answers (4)

domenicr
domenicr

Reputation: 352

You were on the right track.
select event, max(value) from test group by event

Upvotes: 0

TomS
TomS

Reputation: 511

How about SELECT [EVENT], MAX([Value]) FROM [My Table] GROUP BY [EVENT] ?

Upvotes: 1

dwjv
dwjv

Reputation: 1227

Try this out:

SELECT [EVENT]
  ,MAX([Value]) 
FROM [MyTable] 
GROUP BY [EVENT]

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269973

The easiest way to do this is using either a subquery or row_number():

select event, value
from (select t.*, row_number() over (partition by event order by value desc) as seqnum
      from mytable t
     ) t
where seqnum = 1;

or:

select t.*
from mytable t
where t.value = (select max(t2.value) from mytable t2 where t2.event = t.event);

You should be careful about naming columns. event and value might be reserved words in some databases.

Upvotes: 1

Related Questions