SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Select distinct query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

satya068
Posting Yak Master

USA
233 Posts

Posted - 11/08/2012 :  07:07:48  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/08/2012 :  07:11:29  Show Profile  Reply with Quote
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

USA
233 Posts

Posted - 11/08/2012 :  07:17:30  Show Profile  Reply with Quote
Thanks Sunitha.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000