I have a table called signoff in Visual Fox that looks something like this:
id_code rostdate usrid date time status changed unit
WIL4512 18/08/03 JS1 25/08/03 07:36:51 A FALSE 4T
WIL77812 25/08/03 JS1 08/09/03 07:53:02 A FALSE 4ST
WIL122 01/09/03 JS1 08/09/03 07:53:02 A TRUE 4ST
WIIA12 08/09/03 JS1 22/09/03 07:57:43 A FALSE ST
WILLA12 15/09/03 JS1 22/09/03 07:57:43 A FALSE 4EST
WLIA 22/09/03 JS1 03/10/03 09:06:03 A TRUE 4EST
WILLIA12 29/09/03 JS1 03/10/03 09:06:03 A FALSE 4EST
WILLI485 06/10/03 JS1 20/10/03 07:57:24 A FALSE 4ST
WLIA12 13/10/03 JS1 20/10/03 07:57:24 A FALSE 4T
WI8992 20/10/03 JS1 03/11/03 08:33:17 A FALSE 4T
:
:
:
:
Some of the entries in the table are duplicated.
I want remove the duplicated record such, that only the records with the least data is deleted.
I have this much code so far:
Select Id_code, Rostdate, Usrid, Date, Time, Status, Changed, Unit, count(Id_code) from signoff order by id_code group by id_code, Rostdate, Unit
(I want code to use from the command prompt)
this shows me which records are duplicated (ie have the same Id_code, Rostdate and Unit). In the duplicated records, are like this:
id_code rostdate usrid date time status changed unit
WIL4512 18/08/03 JS1 25/08/03 07:36:51 A FALSE 4T
WIL4512 18/08/03 - - TRUE 4T
the code above gives me the secound line and the number of times it was duplicated, but I want the 1st line so that, I can remove all duplicate entries of the original table and use a command to get the 1st line so that I can append it on after the duplicate entries have been deleted.
Can someone please help me?What is the best way to go about it?
Please find tables attached.
