issue with selecting unique - 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 2000 Forums

Import/Export (DTS) and Replication (2000)

issue with selecting unique

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-10-09 : 23:17:24

Here I have a set of data and I need to export these dataset from excel to sql server table.Here When I need to select only unique emp_id with maximum exam_id but even when I use the distinct its populating the second one


	Emp_id 		exam_id  	Grade_obtained
	0001002492	1903956		Grade B	
	0001002492	1903956		
	0001002492	1903956		Grade A	
	0001049121	1963794		Grade B	
			1963344		Grade C	
			1963444		Grade D

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-10-10 : 00:55:01

This select statement is taking too much of time

SELECT DISTINCT emp_id,exam_id, Grade_obtained, CompletedDate,

FROM [Results$] R
WHERE
(NOT (emp_id IS NULL)) AND (exam_id=

(SELECT MAX(exam_id)

FROM [Results$] S

WHERE S.emp_id= R.emp_id
GROUP BY S.emp_id))

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-10 : 13:12:13

i believe this will be faster:


select DISTINCT emp_id,exam_id, Grade_obtained, CompletedDate
from [Results$] R
      inner join (SELECT MAX(exam_id) as exam_id, emp_id FROM [Results$] group by emp_id ) S 
      on (S.emp_id = R.emp_id) and (S.exam_id = R.exam_id) and (R.emp_id is not null)

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources