|
I managed to get my huge query to return results but still get too many duplicates. How can I write a stored procedure that will eliminate all duplicate records except for duplicates in EducationLevelDescription, Emp, and LanguageCodeDescription?
(comments are locked)
|
|
You can also join the duplicates table in and use the ROW_NUMBER function with a partition and only select the values that = 1 in the row number. It's a quick way to eliminate duplicates. I hadn't thought of that one, it sounds good. Do you think that is faster than doing a distinct subquery and joining in the nondistinct columns?
Feb 25 '10 at 01:56 PM
TimothyAWiseman
I don't think it's always faster, but I've seen lots of instances where it is faster, yes.
Feb 25 '10 at 02:01 PM
Grant Fritchey ♦♦
Thank you. I'll give joining the duplicates table a try.
Feb 26 '10 at 12:50 PM
technette
(comments are locked)
|
|
You need to determine what is causing your duplicates first. Do you have duplicate empids in the employee table, or are they coming from somewhere else? Also, please format your code so it can be easily read. There is a code button that can be used to maintain code formatting when submitting your questions and answers. Sorry about the format. I'll correct that next time. The duplicates occur because for each Distinct employee Education Level, Learning Institution, Language, a new row is returned. I will allow the additional row because of educational level but want to delete the other additional rows that are returned for language..etc.
Feb 24 '10 at 08:43 PM
technette
(comments are locked)
|
|
There are a couple of ways of going about it. The one that will give you the most direct solution is to join everything except the education level, learning instution, and language in a view, CTE, or subquery with distinct, and then join in the others that don't have to be distinct in the outer query without using distinct in the outer query. Another option is to concatenate the values for education level, learning instution, and language view so that they appear as comma separate values inside of one cell. If you want to explore that option, then Jeff Moden has a great article at: http://www.sqlservercentral.com/articles/Test+Data/61572/
(comments are locked)
|


did you see the extra answer I put on your previous question? It illustrates the different joins you can use