SQL Statement to select Distinct from Audit Table

I have that is being populated by a trigger.  The action field will contain an "I' (Insert), "U" (Update), or "D" (Delete).  I need to perform a select on that table. and display the last I, U, and D in that table.  So, no matter how many rows there are in there, only 3 should be displayed by the select statement.

Any ideas?
AuditTable.jpg
LVL 2
CipherISAsked:
Who is Participating?
 
Brendt HessSenior DBACommented:
This code should do what you need:


SELECT *
FROM AuditTable
WHERE TestCpkid in (
    SELECT Max(TestCpkid) FROM AuditTable Where Action = 'I'
    UNION
    SELECT Max(TestCpkid) FROM AuditTable Where Action = 'U'
    UNION
    SELECT Max(TestCpkid) FROM AuditTable Where Action = 'D'
    )

Open in new window

0
 
Brendt HessSenior DBACommented:
Of course, you may want to create an index by Action and TestCpkid Desc.  In that case, you could use the UNION of three TOP 1 select statements, e.g.

CREATE INDEX ix_Action_pkid ON AuditTable (Action, TestCpkid Desc)

SELECT TOP 1 * FROM AuditTable
WHERE Action = 'I'
ORDER BY TestCpkid DESC
UNION
SELECT TOP 1 * FROM AuditTable
WHERE Action = 'U'
ORDER BY TestCpkid DESC
UNION
SELECT TOP 1 * FROM AuditTable
WHERE Action = 'D'
ORDER BY TestCpkid DESC
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.