Two same queries to find duplicate records return diffrent result

2278 views sql-server

I have two queries two find the duplicate records but they are returning different results, on the same table

Query 1 Returns 17 rows

SELECT minimumexperience FROM minimumexperience GROUP BY minimumexperience HAVING COUNT(minimumexperience) > 1

Query 2 Returns 22 rows

    SELECT id, 
           ROW_NUMBER() OVER(PARTITION BY lower(Trim(minimumexperience))
           ORDER BY minimumexperience) AS counts
    FROM minimumexperience --ORDER BY dbo.minimumexperience.minimumexperience ASC   
) AS b
WHERE b.counts > 1

answered question

Please include some sample data.

1 Answer


If a given minimumexperience value could occur more than twice, e.g. three or four times, then your second query would return all duplicates besides the first one. For example, if there were four records with a certain value of minimumexperience:

minimumexperience | ROW_NUMBER
10                | 1
10                | 2
10                | 3
10                | 4

Then the first query would report one duplicate, while the second would report three duplicates, because three records have a row number greater than one.

If you really wanted to do this using analytic functions, because you wanted to find all duplicate records, then COUNT would be more appropriate:

    SELECT id, minimumexperience, 
       COUNT(*) OVER (PARTITION BY minimumexperience) cnt
    FROM minimumexperience
) AS b
WHERE b.counts > 1;

posted this

Have an answer?


Please login first before posting an answer.