November 29, 2006 at 10:06 am
I have a student test results table where I have some students with identical rows with the exception of once column (LEP_CODE). The possible values in the LEP_CODE column are 'A' through 'E'. I'm trying to create the sql to evaluate the two rows and delete the second row with lowest LEP_CODE ('A' being the highest value).
Looking for suggestions on how to approach this scenario.
Thanks
November 29, 2006 at 10:32 am
I'd do something like this:
DELETE t1
-- SELECT t1.*, t2.*
FROM table t1
JOIN table t2
ON t1.col1 = t2.col1
AND t1.col2 = t2.col2
AND t1.col3 = t2.col3
... (etc)
AND t1.LEP_CODE < t2.LEP_CODE
highlight and run from the SELECT statement down first to confirm that it lists those records you want to delete.
November 29, 2006 at 11:32 am
All the data is in one table.
November 29, 2006 at 11:37 am
Correct - the suggestion is to do a self join
November 29, 2006 at 11:39 am
d'oh!!! mea culpa
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply