NEED HELP - 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

NEED HELP

Author

Topic

GaneshRamanan
Starting Member

40 Posts

Posted - 2011-02-11 : 03:09:01

SELECT *FROM MRM.EMPMOVEMENT WHERE ID=24681

ID RECID STARTDATE ENDDATE DEPTID
24681 76560 2011/01/02 2011/01/03 1IP-201-A
24681 76560 2011/01/03 2011/01/10 1IP-213-A
24681 78574 2011/01/11 2011/01/11 NULL
24681 78574 2011/01/11 2011/01/13 1IP-213-A

SELECT RECID,DEPTID FROM MRM.EMPMOVEMENT WHERE ID=24681 AND DEPTID IS NOT NULL GROUP BY ID,DEPTID,RECID
76560 1IP-201-A
76560 1IP-213-A
78574 1IP-213-A

I need the TOP 1 REC FOR EACH RECID

output like this

ID RECID STARTDATE ENDDATE DEPTID
24681 76560 2011/01/03 2011/01/10 1IP-213-A
24681 78574 2011/01/11 2011/01/13 1IP-213-A

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-02-11 : 03:18:30

If you are using SQL Server 2005 or higher, you can use the ROW_NUMBER() function.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-02-11 : 03:23:13

Refer this post
http://beyondrelational.com/blogs/madhivanan/archive/2008/09/12/return-top-n-rows.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources