Unanswered: deleting duplicate reocrds with RANK() OVER
I'm working on a table that someone before me managed to duplicate several times and I need to trim it back down to a single record per ID. I opted for RANK() OVER since there is also no primary key. From what I know of SQL and the additional research I did, the code should work, but I get an error message (3075) that I'm missing an operator.
Any light shed on this would be greatly appreciated.
SELECT School, Grade, StudID, LastName, FirstName, Score
(SELECT StudID, LastName, FirstName,
RANK() OVER (PARTITION BY LastName, FirstName
ORDER BY StudID ASC) AS SeqNumber
(SELECT StudID, LastName, FirstName
WHERE (LastName, FirstName) IN
(SELECT LastName, FirstName,
GROUP BY LastName, FirstName, StudID
HAVING COUNT(*) > 1)))
WHERE SeqNumber > 1;