SQL to get latest result from each ID - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL to get latest result from each ID

Author

Topic

genelam
Starting Member

2 Posts

Posted - 2008-02-06 : 16:05:46

Hi,

this will be my first post to sqlteam.com.
I've been trying to work out this problem for awhile, and figured I really need help. I have a set of data that shows the results of a number of tests that I have been doing on a software. Each test I have done records the test_ID, test_Result, and the date_Tested. An example of the columns would be like this:

test_ID | test_Result | date_Tested
3112 | PASS | 2007-11-23 09:29:40.230
3112 | FAIL | 2007-02-22 09:22:00.230
3112 | FAIL | 2007-01-21 09:21:40.234
3113 | PASS | 2007-11-23 09:29:40.230
3114 | PASS | 2007-11-23 09:29:40.230
3115 | FAIL | 2008-01-23 09:29:40.230
3115 | PASS | 2007-11-23 09:29:40.230
3116 | FAIL | 2007-12-25 09:29:40.230
3116 | PASS | 2007-11-23 09:29:40.230

Now what I want is ONLY the latest result for each test_ID, I do not want repeated test_IDs to show up. So my expected result should be:

test_ID | test_Result | date_Tested
3112 | PASS | 2007-11-23 09:29:40.230
3113 | PASS | 2007-11-23 09:29:40.230
3114 | PASS | 2007-11-23 09:29:40.230
3115 | FAIL | 2008-01-23 09:29:40.230
3116 | FAIL | 2007-12-25 09:29:40.230

Is there a way I can query this? I've tried using "distinct test_ID" but it would still show the repeated test_ID due to the date_Tested not being the same.. also Group By will not work. I'm not sure what to do here. Any ideas anyone?

Thanks in advance.

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-02-06 : 16:12:17

If you are using Sql 2005:

SELECT  test_Id,
        test_Result,
        date_Tested
FROM    ( SELECT    ROW_NUMBER() OVER ( PARTITION BY test_ID ORDER BY date_Tested ) AS rn,
                    test_Id,
                    test_Result,
                    date_Tested
          FROM      YourTable
        ) a
WHERE   rn = 1

genelam
Starting Member

2 Posts

Posted - 2008-02-06 : 16:36:14

Thanks! Thats exactly what I was looking for. Just a question, why is it necessary to have "a" after the inner select clause?

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-02-06 : 16:46:57

Sql Server requires that derived tables have a corresponding table alias. In this case the "a" is there merely to fulfill that requirement.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-07 : 07:49:52

And see what you can do with row_number()
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources