Select distinct query - SQL Server Forums

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;

satya068
Posting Yak Master

233 Posts

Posted - 2012-11-08 : 07:17:30

Thanks Sunitha.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources