Solved

Get Top record for a set of records ...

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

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 25
Database-Scoped Permissions 2 19
SQL Query Across Multiple Tables - Help 5 25
SQL Server Serialization error 8 16
In this article I will describe the Backup & Restore 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.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decadeā€¦

696 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