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
 How should I write this query?

Author  Topic 

dboor129
Starting Member

3 Posts

Posted - 2015-04-01 : 19:25:39
I have one table called 'Employee' that has 3 columns which are birthdate, employment_date, and name. How do i create a query that show the YOUNGEST and OLDEST person at the time of employment?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2015-04-02 : 01:16:05

select t1.* from table as t1 inner join
(select min(birthdate) as min_birth,max(birthdate) as max_birth from table) as t2
on t1.birthdate in (t2.min_birth,t2,max_birth)

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-04-02 : 04:31:01
Method 1:
select top(1) *
from employee
order by datediff(dd,birthdate,employment_date)
union all
select top(1) *
from employee
order by datediff(dd,birthdate,employment_date) desc
Method 2:
select name
,birthday
,employment_date
from (select *
,row_number() over(order by datediff(dd,birthdate,employment_date)) as rn_min
,row_number() over(order by datediff(dd,birthdate,employment_date) desc) as rn_max
from employee
) as a
where rn_min=1
or rn_max=1

Ps: I have no access to a database server to test the syntax, but should be ok (I think)
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2015-04-02 : 09:50:08
I understood the question to not be Youngest / Oldest employee now, but Youngest / Oldest employee at the time that EACH Employee was hired, viz:

"How do i create a query that show the YOUNGEST and OLDEST person at the time of employment?"

which is a slightly more challenging query
Go to Top of Page
   

- Advertisement -