October 8, 2009 at 2:53 pm
How can I get the maximum value in a group? Here I have this:
SELECT A, B, C, MAX(F)
FROM TblX X
LEFT JOIN TblY Y
ON X.A = Y.A
GROUP BY A, B, C
This gives me a result that looks like:
Col A--------Col B--------Col C--------Col F
Same1------Same1------Same1--------10
Same1------Same1------Same1--------12
Same2------Same2------Same2--------32
Same2------Same2------Same2--------42
The results are supposed to only be the rows with the largest of F:
Col A--------Col B--------Col C--------Col F
Same1------Same1------Same1--------12
Same2------Same2------Same2--------42
I know I need to have HAVING Max(F) = ? at the end, but I'm not sure how to do it.
October 8, 2009 at 9:45 pm
Hi,
just try with
select colA,colB,colC,max(ColF)
from mytable
group by colA,colB,colC
October 9, 2009 at 7:08 am
Thanks, that outputs, but it embarrasses me to say that I forgot to mention another column in the mix, Column D has ungroupable variances. It can't be grouped.
The code you wrote is good, if there were no variances in column D, but there is also:
select colA,colB,colC,colD,max(ColF)
from mytable
group by colA,colB,colC,colD
yields this:
Col A--------Col B--------Col C--------Col D--------Col F
Same1------Same1------Same1-------random--------10
Same1------Same1------Same1-------someth--------12
Same2------Same2------Same2-------anythin--------32
Same2------Same2------Same2-------morelse--------42
This is what is desired:
Same1------Same1------Same1-------someth--------12
Same2------Same2------Same2-------morelse--------42
Obviously I don't want to group by colD:
select colA,colB,colC,ColD,max(ColF)
from mytable
group by colA,colB,colC
Yet when I do that I get: "Column 'ColD' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."
October 9, 2009 at 9:23 pm
create table #temp
(
col1 varchar(10),
col2 varchar(10),
col3 varchar(10),
col4 varchar(10),
col5 int
)
insert into #temp
select 'A','AA','AAA','KA',10
union all
select 'A','AA','AAA','KB',20
union all
select 'A','AA','AAA','KC',30
union all
select 'B','BB','BBB','LB',40
union all
select 'B','BB','BBB','LC',50
union all
select 'B','BB','BBB','LD',60
select a.col1,a.col2,a.col3,a.col4,a.col5
from #temp a,
(
select a.col1,a.col2,a.col3,max(a.col5)col5
from #temp a
group by a.col1,a.col2,a.col3
) b
where b.col1 =a.col1
and b.col2 = a.col2
and b.col3 = a.col3
and b.col5 = a.col5
order by a.col1,a.col2,a.col3
October 9, 2009 at 10:02 pm
Arun's solution is how you would accomplish leaving colD unaggregated and in the query. However, it seems (from your requested output) like you only want 1 of colD, not all of them. In that case, you would need to figure out which colD you wanted. Wrapping colD in a MAX as well should give you the result set you requested. If you have more complex criteria for which colD to keep, you may need to use another derived table to process that.
October 12, 2009 at 2:22 pm
Thanks so very much Arun! I'm not used to using sub-tables, very nice to learn from simple examples.
Seth Phelabaum, thanks also. I just wanted the ColD that was on the row that had the greatest ColF. I think that's what Arun did. But I see what you mean about another derived table if I wanted the largest ColD as well. I'm not sure what you mean by "only want 1 of colD." The code above seemed to keep it only as one already, but I might be short sighted for a scenario where a second ColD slips in?
October 12, 2009 at 2:31 pm
saivko (10/12/2009)
Thanks so very much Arun! I'm not used to using sub-tables, very nice to learn from simple examples.Seth Phelabaum, thanks also. I just wanted the ColD that was on the row that had the greatest ColF. I think that's what Arun did. But I see what you mean about another derived table if I wanted the largest ColD as well. I'm not sure what you mean by "only want 1 of colD." The code above seemed to keep it only as one already, but I might be short sighted for a scenario where a second ColD slips in?
The reason it appears to only have 1 ColD is because the data only supplies 1 per max colF value. If you add an extra line to your test data, say:
select 'A','AA','AAA','KN',30
You'll now have 2 colD's for the single Max ColF of 30 and will get dupes. Just wanted to point it out.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply