Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Having and Max of a column Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, October 8, 2009 2:53 PM
 SSC Rookie Group: General Forum Members Last Login: Thursday, October 28, 2010 1:10 PM Points: 48, Visits: 141
 How can I get the maximum value in a group? Here I have this:SELECT A, B, C, MAX(F)FROM TblX XLEFT JOIN TblY YON X.A = Y.A GROUP BY A, B, CThis gives me a result that looks like:Col A--------Col B--------Col C--------Col FSame1------Same1------Same1--------10Same1------Same1------Same1--------12Same2------Same2------Same2--------32Same2------Same2------Same2--------42The results are supposed to only be the rows with the largest of F:Col A--------Col B--------Col C--------Col FSame1------Same1------Same1--------12Same2------Same2------Same2--------42I know I need to have HAVING Max(F) = ? at the end, but I'm not sure how to do it.
Post #800381
 Posted Thursday, October 8, 2009 9:45 PM
 Ten Centuries Group: General Forum Members Last Login: Monday, July 25, 2016 5:45 AM Points: 1,229, Visits: 3,493
 Hi,just try withselect colA,colB,colC,max(ColF)from mytablegroup by colA,colB,colC
Post #800486
 Posted Friday, October 9, 2009 7:08 AM
 SSC Rookie Group: General Forum Members Last Login: Thursday, October 28, 2010 1:10 PM Points: 48, Visits: 141
 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 mytablegroup by colA,colB,colC,colDyields this:Col A--------Col B--------Col C--------Col D--------Col FSame1------Same1------Same1-------random--------10Same1------Same1------Same1-------someth--------12Same2------Same2------Same2-------anythin--------32Same2------Same2------Same2-------morelse--------42This is what is desired:Same1------Same1------Same1-------someth--------12Same2------Same2------Same2-------morelse--------42Obviously I don't want to group by colD:select colA,colB,colC,ColD,max(ColF)from mytablegroup by colA,colB,colCYet 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."
Post #800709
 Posted Friday, October 9, 2009 9:23 PM
 Ten Centuries Group: General Forum Members Last Login: Monday, July 25, 2016 5:45 AM Points: 1,229, Visits: 3,493
 create table #temp(col1 varchar(10),col2 varchar(10),col3 varchar(10),col4 varchar(10),col5 int)insert into #tempselect 'A','AA','AAA','KA',10union allselect 'A','AA','AAA','KB',20union allselect 'A','AA','AAA','KC',30union allselect 'B','BB','BBB','LB',40union allselect 'B','BB','BBB','LC',50union allselect 'B','BB','BBB','LD',60select a.col1,a.col2,a.col3,a.col4,a.col5from #temp a,(select a.col1,a.col2,a.col3,max(a.col5)col5from #temp agroup by a.col1,a.col2,a.col3) bwhere b.col1 =a.col1 and b.col2 = a.col2and b.col3 = a.col3and b.col5 = a.col5order by a.col1,a.col2,a.col3
Post #801155
 Posted Friday, October 9, 2009 10:02 PM
 SSCommitted Group: General Forum Members Last Login: Tuesday, August 2, 2016 2:14 PM Points: 1,519, Visits: 4,107
 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. Seth PhelabaumConsistency is only a virtue if you're not a screwup. Links: How to Post Sample Data :: Running Totals :: Tally Table :: Cross Tabs/Pivots :: String Concatenation
Post #801161
 Posted Monday, October 12, 2009 2:22 PM
 SSC Rookie Group: General Forum Members Last Login: Thursday, October 28, 2010 1:10 PM Points: 48, Visits: 141
 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?
Post #801820
 Posted Monday, October 12, 2009 2:31 PM
 SSCommitted Group: General Forum Members Last Login: Tuesday, August 2, 2016 2:14 PM Points: 1,519, Visits: 4,107
 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',30You'll now have 2 colD's for the single Max ColF of 30 and will get dupes. Just wanted to point it out. Seth PhelabaumConsistency is only a virtue if you're not a screwup. Links: How to Post Sample Data :: Running Totals :: Tally Table :: Cross Tabs/Pivots :: String Concatenation
Post #801825

 Permissions