Group by Help Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Group by Help Query

Author

Topic

Deepak1983
Starting Member

23 Posts

Posted - 2010-10-25 : 06:46:28

RowNum Ref_No Date
1 1011 1/1/2010
2 1011 2/1/2011
3 1011 1/8/2009
1 1012 5/5/2010
1 1013 4/5/2010
2 1013 6/8/2010

This is Simple table I have marked rows as Red that I want, Which means I want Max(RowNum) and respective Ref_No and Date using single query.

Thanks in advance for help.

Deeps

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-25 : 06:55:48

Something to start with:

Select * from MyTable M where RowNum =
(Select max(RowNum) from MyTable ST
where ST.RowNum = M.RowNum and ST.Ref_no = M.Ref_No)

Deepak1983
Starting Member

23 Posts

Posted - 2010-10-25 : 08:49:25

Your code gives me all data back again where I want only max and relevant details. But thanks to giving me this idea I did the same using CTE and re joining the same table with itself. Its complex to exlain here.

Anyway Thanks A lot !!!

quote:
Originally posted by pk_bohra

Something to start with:

Select * from MyTable M where RowNum =
(Select max(RowNum) from MyTable ST
where ST.RowNum = M.RowNum and ST.Ref_no = M.Ref_No)

Deeps

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-25 : 09:35:45

Hi Deepak,

You were right.The select statement was incorrect.

Any how as you said you got the solution. Try this and check the performance.

Declare @MyTable table
(RowNum int,
Ref_no int,
Date datetime)

Insert into @MyTable
Select 1, 1011, '2010-01-01' union
Select 2, 1011, '2011-01-02' union
Select 3, 1011, '2009-08-01' union
Select 2,1012,'2010-05-05' union
Select 2, 1013,'2010-05-04' union
Select 3, 1013,'2010-08-06'

Select M.* from @MyTable M inner join
(Select Ref_no,max(RowNum) as RowNum from @MyTable
Group by REf_no) as ST
On M.RowNum = ST.Rownum and M.Ref_no = ST.Ref_no
Order by M.Ref_no

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

Deepak1983
Starting Member

23 Posts

Posted - 2010-10-25 : 10:57:15

Yes Now it is right. This is similar to what I did, the only this is i have given you a simple table as for example where I dont have it over here in my work. I have query where I am using Row_Number() with Partition with Ref Num and Date.
so I used for CTE and then your query.

Thanks,
Deepak

quote:
Originally posted by pk_bohra

Hi Deepak,

You were right.The select statement was incorrect.

Any how as you said you got the solution. Try this and check the performance.

Declare @MyTable table
(RowNum int,
Ref_no int,
Date datetime)

Insert into @MyTable
Select 1, 1011, '2010-01-01' union
Select 2, 1011, '2011-01-02' union
Select 3, 1011, '2009-08-01' union
Select 2,1012,'2010-05-05' union
Select 2, 1013,'2010-05-04' union
Select 3, 1013,'2010-08-06'

Select M.* from @MyTable M inner join
(Select Ref_no,max(RowNum) as RowNum from @MyTable
Group by REf_no) as ST
On M.RowNum = ST.Rownum and M.Ref_no = ST.Ref_no
Order by M.Ref_no

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

Deeps

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources