?
Solved

SQL Statement to select Distinct from Audit Table

Posted on 2010-11-19
2
Medium Priority
?
300 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:CipherIS
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
2 Comments
 
LVL 32

Accepted Solution

by:
Brendt Hess earned 500 total points
ID: 34175055
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
 
LVL 32

Expert Comment

by:Brendt Hess
ID: 34175084
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

Featured Post

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question