Solved

Get Top record for a set of records ...

Posted on 2011-09-09
5
344 Views
Last Modified: 2012-05-12
Hi Experts,

i have an issues with a query im trying to write, what i am looking for is something like this:

1     Test1      Sucess      event 1    09/09/2011
2     Test2      Success    event 1    10/09/2011
3     Test1      Success    event 1    10/09/2011
4     Test3      Failed       event 2    10/09/2011
5     Test3      Success    event 1    10/09/2011

What im trying to achieve is only bring back the most recent record for event 1 for a particular test im running.

Can someone please help me with the query im tryung to run.

Thank you in advance.



Trevor
0
Comment
Question by:trevsoft
5 Comments
 
LVL 9

Expert Comment

by:sarabhai
ID: 36514941
SELECT * FROM T_TABLE WHERE EVENT = 'event 1' AND ID = ( SELECT MAX(ID) FROM T_TABLE )

0
 
LVL 22

Expert Comment

by:Thomasian
ID: 36514976
SELECT TOP 1 *
FROM tablename
WHERE [TestColumn] = 'Test1'	--Filter test here
      AND [EventColumn] = 'event 1'	--Filter event here
ORDER BY [DateColumn] DESC

Open in new window

0
 
LVL 1

Author Comment

by:trevsoft
ID: 36515029
Hi Guys,

I only want to bring back one record for each test though. Like that the result set will be the most recent result. So 1 record for every test column.



Trevor
0
 
LVL 22

Accepted Solution

by:
Thomasian earned 500 total points
ID: 36515085
SELECT *
FROM tablename T
WHERE [EventColumn] = 'event 1'
      AND [IdentityColumn] = (SELECT TOP 1 [IdentityColumn]
                              FROM tablename
                              WHERE [EventColumn] = 'event 1'
                                    AND [TestColumn] = T.[TestColumn]
                              ORDER BY [DateColumn] DESC)

Open in new window

If you are using SQL Server 2005 or later, you can also use ROW_NUMBER
WITH CTE AS (
	SELECT *, rn = ROW_NUMBER() OVER (PARTITION BY [TestColumn] ORDER BY [DateColumn] DESC)
	FROM tablename
	WHERE [EventColumn] = 'event 1'	--Filter event here
)
SELECT *
FROM CTE
WHERE rn=1

Open in new window


0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36515888
you may want to clarify if you are working with mysql or mssql.

anyhow, you can read this article to check your options:
http://www.experts-exchange.com/A_3203.html
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

861 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