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

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

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)

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources