# How to Get 7th eldest employee detail by date?

### Question

• How to Get  7th eldest employee detail by date?
Friday, November 15, 2013 4:48 AM

• ```SELECT top 1 * from
(select top 7 * from employeetable  order by birthday asc) as a
order by birthday desc```
hope this helps..
• Marked as answer by Friday, November 15, 2013 5:56 AM
Friday, November 15, 2013 5:27 AM
• You can use the DATEDIFF function for that, eg

```;WITH cte AS
(
SELECT *, DATEDIFF( year, DOJ, GETDATE() ) years_experience
FROM dbo.HRMEmployeeMaster
WHERE DATEDIFF( month, DOJ, GETDATE() ) > (4 * 12)
)
SELECT *
FROM cte```

• Edited by Friday, November 15, 2013 6:10 AM changed to month
• Marked as answer by Friday, November 15, 2013 6:10 AM
Friday, November 15, 2013 6:05 AM

### All replies

• Try

```with cte as
(select top 7 birthday,a.*
from emp_tab
order by 1)
select top 1 *
from cte
order by 1 desc```

Many Thanks & Best Regards, Hua Min

Friday, November 15, 2013 5:16 AM
• Try the below:

```create Table test_Nov15_1(Empid int, DOB Date)

Insert into test_Nov15_1
Values(1,GETDATE())
,(234,GETDATE()-1)
,(235,GETDATE()-100)
,(456,GETDATE()-200)
,(123,GETDATE()-47)
,(5682,GETDATE()-20)
,(685,GETDATE()-454)
,(3,GETDATE()-2)
,(453,GETDATE()-200)

Select * From test_Nov15_1 Order by DOB desc
;With cte
as
(
Select *, DENSE_RANK()Over(order by DOB desc) Rn
From test_Nov15_1
) Select * From cte where rn=7

Drop table test_Nov15_1```

Friday, November 15, 2013 5:17 AM
• This is hard for us to guess without seeing and DDL or sample data, but here's an example using AdventureWorks2008r2 which might help:

```USE AdventureWorks2008R2

;WITH cte AS(
SELECT RANK() OVER( ORDER BY BirthDate ) rn, *
FROM [HumanResources].[Employee]
)
SELECT *
FROM cte
WHERE rn = 7```

Friday, November 15, 2013 5:23 AM
• try,

```select * From employee where
dob =( select min(dob) from (select top 7 dob from employee order by dob asc) A )```

Friday, November 15, 2013 5:26 AM
• ```SELECT top 1 * from
(select top 7 * from employeetable  order by birthday asc) as a
order by birthday desc```
hope this helps..
• Marked as answer by Friday, November 15, 2013 5:56 AM
Friday, November 15, 2013 5:27 AM
• Friday, November 15, 2013 5:33 AM
• Try the below:(to accommodate your actual tables)

```;With cte
as
(
Select *, DENSE_RANK()Over(order by DOJ desc) Rn
From [cutechappsdb].[dbo].[HRMEmployeeMaster]
) Select * From cte where rn=7```

• Proposed as answer by Friday, November 15, 2013 5:48 AM
Friday, November 15, 2013 5:42 AM
• sorry for inconvenience all are giving different queries results are different how can i take?
Friday, November 15, 2013 5:54 AM
• Its easy, check yourself the result and find the result that is matching with your desired output.

Different queries shows the different approaches to your problem, you may choose the best.

But of course, there will not be different result, that should be one as per your desired/expected result.

Friday, November 15, 2013 5:58 AM
• How to Get employees who experienced  more than 4 years?

Friday, November 15, 2013 5:59 AM
• You can use the DATEDIFF function for that, eg

```;WITH cte AS
(
SELECT *, DATEDIFF( year, DOJ, GETDATE() ) years_experience
FROM dbo.HRMEmployeeMaster
WHERE DATEDIFF( month, DOJ, GETDATE() ) > (4 * 12)
)
SELECT *
FROM cte```

• Edited by Friday, November 15, 2013 6:10 AM changed to month
• Marked as answer by Friday, November 15, 2013 6:10 AM
Friday, November 15, 2013 6:05 AM
• Thank you for your query can Explain this query with few words?
Friday, November 15, 2013 6:14 AM