Solved

SQL Statement to select Distinct from Audit Table

Posted on 2010-11-19
2
287 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:
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Are triggers slow? 7 27
How to keep a record with the highest value 3 56
Find unused columns in a table 12 90
Usage Scenarios for Extended Events - Tracking Recompilations 1 45
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
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.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

739 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