Get the ID of the row with Max(fieldname)
-
Hi,
I need to get the ID of the rows containing the max value of a field within a group. Ideally I'd like to return the row in entirety but just the ID would do. As an example, if I have:
id | MyGroup | MyData
--------------------------
1 | a | 10
2 | a | 20
3 | a | 30
4 | b | 10
5 | b | 20
6 | b | 30
7 | b | 40
I would like to have this returned:
id | MyGroup | MyData
--------------------------
3 | a | 30
--------------------------
7 | b | 40
--------------------------
(where id 3 contains the highest data in group a and id 7 has the highest data in group b...etc.)
I tried using SELECT ID, MAX(MyData) ...... GROUP BY MyGroup but get an Error because ID is not in the GROUP BY clause. If I insert it there I get the wrong results.
Any help appreciated.
-
You're on the right track, almost. But you can't use ID here, you have to group by mygroup to find the max of mydata - then you can use those two to find the rows. One way is to use a derived table, like below.
select g.*
from (
select mygroup,
max(mydata) as maxData
from #groups
group by mygroup
) x
join #groups g
on x.mygroup = g.mygroup
and x.maxData = g.mydata
Note that if a group has more than one row with the same value for max(mydata), both will be returned.
/Kenneth
-
I think you can use this query do the same thing:
create table #mytbl(id int identity, mygroup char(1),mydata int)
select *
from #mytbl
where mygroup+cast(mydata as char(2)) in
(select mygroup+max(cast(mydata as char(2))) from #mytbl group by mygroup)
Just change char(2) to char whatever number.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply