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)
 Select distinct query

Author  Topic 

satya068
Posting Yak Master

233 Posts

Posted - 2012-11-08 : 07:07:48
select DISTINCT EMP_NUMBER
--emp_date, join_date ,estimated_date, create_date,current_flag
from EMP
WHERE DATEDIFF(YEAR,person_date_of_birth,join_DATE) >=65
when i run this query i am able to get all distinct emp numbers

How can i get rest of the fields data for that distinct EMP_Number
from the query

I need

EMP_Number Emp_Date Join_Date Estimated_Date Create_Datde Current_F

Thanks


sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-08 : 07:11:29
You can change the "ORDER BY emp_date" in the query below to change which of the many records you want to pick. It will pick the first one based on the order by clause
SELECT * FROM
(

select DISTINCT EMP_NUMBER
emp_date, join_date ,estimated_date, create_date,current_flag
,ROW_NUMBER() OVER (PARTITION BY EMP_NUMBER ORDER BY emp_date) AS RN
from EMP
WHERE DATEDIFF(YEAR,person_date_of_birth,join_DATE) >=65
) s WHERE RN = 1;
Go to Top of Page

satya068
Posting Yak Master

233 Posts

Posted - 2012-11-08 : 07:17:30
Thanks Sunitha.
Go to Top of Page
   

- Advertisement -