Selecting problem
-
Hi...I am wondering if you can help me with this database problem, please.
The table below contains references to master controls in a form. Master control names should be unique within a master form but some bad client code duplicated them. The task is to remove duplicated “szControlName” records and leave only the latest control names for each master form. Once I figure out the SELECT to get the latest dated/non-duplicated records, I can write code to copy to another table. I need something like “select the MAX ? dCreated for each different grouping of lMasterForm, lCreatedBy, lControlType, szControlName. It doesn’t look to bad to me, it is just that I am more of a developer than DBA.
For example, looking at lMasterForm 258 (first red block), I want to remove IDs from 14648 to 14651 and only leave the different sControlName records with the latest IDs (14652 and 14653)
Another example (second red block), I want to remove IDs 14654 and 14655 for lMasterForm=259 and leave only IDs 14656 and 14657 (latest dCreated dates for each different control name on that master form).
SELECT *
FROM MASTER_FORM_CONTROL
ORDER BY lMasterForm, dCreated, szControlName
-
Since your image was not visible I hope this is what you are looking for>
SElect MASTER_FORM_CONTROL.* from MASTER_FORM_CONTROL INNER JOIN (
SELECT lMasterForm,szControlName,max(dCreated) as MaxdCreated
FROM MASTER_FORM_CONTROL
Group by lMasterForm,szControlName) DeDupe on MASTER_FORM_CONTROL.lMasterForm = DeDupe.lMasterForm
and MASTER_FORM_CONTROL.szControlName = DeDupe.szControlName and MASTER_FORM_CONTROL.dCreated = DeDupe.MaxdCreated
ORDER BY lMasterForm, dCreated, szControlName
-
Thanks a lot Sreejith, even without the image, you found the solution, this is working great and it is all I needed, I only added a DELETE clause to remove the un-wanted records.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply