Solved

Get Top record for a set of records ...

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

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
In this article I will describe the Copy Database Wizard 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.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

744 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now