Select Top 2 Rows - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Select Top 2 Rows

Author

Topic

obiwaugh
Starting Member

27 Posts

Posted - 2004-06-17 : 17:18:48

I need to select the top two rows for each employee on a job history table. Data in the table could like this:

EMPLID,EFFDT,EFFSEQ,ACTION
1001,10/24/04,1,PAY
1001,10/24/04,0,XFR
1001,10/22/03,0,HIR
1002,04/16/04,0,PAY
1002,02/14/03,1,PAY
1002,02/14/03,0,XFR

If two actions occur on the same day, they are differentiated by the EFFSEQ field. The highest EFFSEQ is the most recent action. I want to return the top 2 rows. Is there a way to rank the rows of data so that each row will have a unique ranking that I can select from?

Thanks!

Pat Phelan
Posting Yak Master

187 Posts

Posted - 2004-06-17 : 17:47:24

Maybe something like:

SELECT EMPLID, EFFDT, EFFSEQ, ACTION
   FROM PS_JOB AS A
   WHERE  2 > (SELECT Count(*)
      FROM PS_JOB AS B
      WHERE  B.EMPLID = A.EMPLID
         AND (B.EFFDT > A.EFFDT
            OR (B.EFFDT = A.EFFDT
               AND B.EFFSEQ > A.EFFSEQ)))
   ORDER BY EMPLID, EFFDT, EFFSEQ

-PatP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources