Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Having and Max of a column Expand / Collapse
Author
Message
Posted Thursday, October 8, 2009 2:53 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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 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.
Post #800381
Posted Thursday, October 8, 2009 9:45 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 19, 2012 10:25 PM
Points: 1,229, Visits: 3,483
Hi,
just try with

select colA,colB,colC,max(ColF)
from mytable
group by colA,colB,colC

Post #800486
Posted Friday, October 9, 2009 7:08 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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 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."
Post #800709
Posted Friday, October 9, 2009 9:23 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 19, 2012 10:25 PM
Points: 1,229, Visits: 3,483
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

Post #801155
Posted Friday, October 9, 2009 10:02 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 2:20 PM
Points: 1,519, Visits: 4,081
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 Phelabaum
Consistency 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

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 2:20 PM
Points: 1,519, Visits: 4,081
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.


Seth Phelabaum
Consistency 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
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse