November 20, 2009 at 1:57 pm
EDIT: Nevermind. I figured it out.
http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql
Given the following data:
X | Y | Z
1 | 1 | 15
2 | 1 | 12
3 | 1 | 9
4 | 2 | 3
5 | 2 | 22
6 | 2 | 14
7 | 3 | 99
8 | 3 | 34
9 | 3 | 66
How can I get X for each group Y where Z = MIN(Z)
My results for the example data would be 3, 4, 8
My problem seems to be when I group by Y and use the aggregate MIN(Z) I can't select X unless I add it to the group by clause, which ruins the grouping, or use it in an aggregate, which isn't right either.
There must be a graceful way to do this.
Thanks for your help.
November 20, 2009 at 2:57 pm
While this may not be the most elegant solution, it does produce the desired result:
declare @table table (x int, y int, z int)
insert into @table (x, y, z)
select 1, 1, 15
union select 2, 1, 12
union select 3, 1, 9
union select 4, 2, 3
union select 5, 2, 22
union select 6, 2, 14
union select 7, 3, 99
union select 8, 3, 34
union select 9, 3, 66
select * from @table
select t1.x
from @table t1
where t1.z = (select min(t2.z) from @table t2 where t2.y = t1.y group by t2.y)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply