I have a table that keeps track of trucks going in and out of our yard...sometimes (because the guards are lax) they will enter the same truck twice...so, at the end of each week I go and find the duplicates with the following query:
SELECT tractorbarcodeid, tractor, trailerbarcodeidin, txndatetimein, driver, txncomplete
FROM a_gatetxntractor
WHERE (tractorbarcodeid IN
(SELECT tractorbarcodeid
FROM a_gatetxntractor
GROUP BY tractorbarcodeid
HAVING COUNT(*) > 1))
ORDER BY tractorbarcodeid, txndatetimein DESC
The following is the result set :
0173 0173 800682 2007-06-22 17:20:44.883 DILLARD, TOM 0
0173 0173 800682 2007-06-22 17:10:29.810 DILLARD, TOM 0
0173 0173 800672 2007-06-22 17:01:52.393 DILLARD, TOM 0
0173 0173 800670 2007-06-22 16:54:39.133 DILLARD, TOM 0
0173 0173 NULL NULL DILLARD, TOM 0
0612 0612 800662 2007-06-22 14:21:11.503 ROMO, TONY 0
0612 0612 800226 2007-06-22 11:58:46.610 ROMO, TONY 0
0612 0612 None 2007-06-22 11:54:05.310 ROMO, TONY 0
0612 0612 800246 2007-06-22 10:37:19.500 ROMO, TONY 0
0612 0612 None 2007-06-22 10:22:37.310 ROMO, TONY 0
400007 L217 801030 2007-06-25 12:51:04.177 MARCO 0
400007 342582 800370 2007-06-19 03:20:44.837 JAMES 0
Well, I can manually go remove the duplicates by setting the txncomplete column to a 1, but is there a way to do this for all rows except for the latest entry..in other words, the first row in my result set is the latest entry..which is the one I want to keep. And I want to set the others to a 1. Is this possible? Do I need to use a cursor?
Thanks.
Start Free Trial
