How do I get Top 2 by Max Date for each EEID? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2005 Forums

Transact-SQL (2005)

How do I get Top 2 by Max Date for each EEID?

Author

Topic

sonyshah
Starting Member

7 Posts

Posted - 2008-06-30 : 19:58:18

Hi, I am new to SQL coding and I want to get Top 2 Records by Max(T1.ejhdatetimecreated) for each EEID. Then, UNION it with same query and get Top 2 MIN(T1.ejhdatetimecreated) for each EEID. Can someone please help me? Thanks

select
T1.ejheeid,
T1.ejhannsalary,
Max(T1.ejhdatetimecreated),
T1.ejhhourlypayrate,
T1.ejhisratechange,
T1.ejhjobeffdate
from dbo.emphjob T1
where T1.ejheeid in ('5GQK0K0000K0','5U8FSP0000K0','5U8FUX0000K0','5U8FX30000K0','5U8GEV0030K0','5U8GKD0000K0','5U8GNG0030K0','5U8GO30000K0','5U8GOR0030K0','5U8GSE0000K0','5U8GXL0000K0','5U8GYT0000K0','5U8H000000K0')
Group By
T1.ejheeid, T1.ejhannsalary,
T1.ejhhourlypayrate,
T1.ejhisratechange,
T1.ejhjobeffdate
order by T1.ejheeid , T1.ejhannsalary

dshelton
Yak Posting Veteran

73 Posts

Posted - 2008-06-30 : 20:50:59

Can you please provide an example of expected results.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-01 : 00:47:55

[code]SELECT *
FROM
(
select row_number() over(partition by T1.ejheeid order by T1.ejhdatetimecreated) AS minseq,
row_number() over(partition by T1.ejheeid order by T1.ejhdatetimecreated desc) AS maxseq,
T1.ejheeid,
T1.ejhannsalary,
T1.ejhdatetimecreated,
T1.ejhhourlypayrate,
T1.ejhisratechange,
T1.ejhjobeffdate
from dbo.emphjob T1
where T1.ejheeid in ('5GQK0K0000K0','5U8FSP0000K0','5U8FUX0000K0','5U8FX30000K0','5U8GEV0030K0','5U8GKD0000K0','5U8GNG0030K0','5U8GO30000K0','5U8GOR0030K0','5U8GSE0000K0','5U8GXL0000K0','5U8GYT0000K0','5U8H000000K0')

)t
WHERE t.minseq<=2
OR t.maxseq<=2[/code]

sonyshah
Starting Member

7 Posts

Posted - 2008-07-01 : 13:53:49

Thanks Vishak. I will play with the SQL. It is a good guideline for me to work with. Thanks again

SS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources