SQLTeam.com | Weblogs | Forums

SELECT Max Num of each Col ID?

How to Query if I only want the rows with max number?

Example below:

MyCol MyNum
A 1
A 2
B 1
B 2
B 3
C 1
C 2
C 3
D 1

I only want as below rows with the max value of MyNum for each MyCol?

MyCol MyNum
A 2
B 3
C 3
D 1

hi

i tried to do this ... hope it helps :slight_smile: :slight_smile:
i love any feedback

drop create data ....
 drop table #data 
 go 

create table #data 
(
MyCol  varchar(100) , 
MyNum  int 
)
go 

 insert into #data select 'A',1
 insert into #data select 'A',2
 insert into #data select 'B',1
 insert into #data select 'B',2
 insert into #data select 'B',3
 insert into #data select 'C',1
 insert into #data select 'C',2
 insert into #data select 'C',3
 insert into #data select 'D',1
 go 

select 'data',* from #data 
go
SQL ...
SELECT 'my output', 
       mycol, 
       Max(mynum) 
FROM   #data 
GROUP  BY mycol 

go

image