Solved

Get Top record for a set of records ...

Posted on 2011-09-09
5
357 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
[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
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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

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.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

726 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