SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Having and Max of a column


Having and Max of a column

Author
Message
saivko
saivko
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 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.
arun.sas
arun.sas
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1657 Visits: 3493
Hi,
just try with

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


saivko
saivko
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 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."
arun.sas
arun.sas
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1657 Visits: 3493
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


Garadin
Garadin
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2713 Visits: 4107
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
saivko
saivko
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 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?
Garadin
Garadin
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2713 Visits: 4107
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search