[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Get Top record for a set of records ...

Posted on 2011-09-09
5
Medium Priority
?
370 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 2000 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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

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.
In this article, we’ll look at how to deploy ProxySQL.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

656 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