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
Solved

SQL Statement to select Distinct from Audit Table

Posted on 2010-11-19
2
274 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
  • 2
2 Comments
 
LVL 32

Accepted Solution

by:
bhess1 earned 125 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:bhess1
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

838 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