most recent dates and results - SQL Server Forums

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

most recent dates and results

Author

Topic

barflyz
Starting Member

47 Posts

Posted - 2010-04-26 : 08:28:43

If I have the data below stored in a transactional system, how do I select the most recent Test date and associated Test Result associated with the date and then select the 2nd most recent and then the third in a column based manner. I know how to use MAX and get the most recent and could continue this method using a “not in” for the second and third but there must be a more efficient way to do this?

Current data:
Cust id
Test date
Test date
Test date
Test result
Test result
Test result
Etc.

Desired results

Cust id test date 1, test result 1, test date 2, test result 2, test date 3, test result 3

thanks

dattatreysindol
Starting Member

20 Posts

Posted - 2010-04-26 : 08:51:47

Hi There -

Try using the SQL Server Ranking functions to get the expected output.

Take a look at this article on msdn for more details on the Ranking Functions available in SQL Server along with Syntax / Examples.

http://msdn.microsoft.com/en-us/library/ms189798.aspx

Dattatrey Sindol
http://mytechnobook.blogspot.com/

This information is provided "AS IS" with no warranties, and confers no rights.

barflyz
Starting Member

47 Posts

Posted - 2010-04-26 : 09:09:18

I don't fully follow but will review again. Anything else in a simple manner is appreciated. We could use cursor? correct?

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-04-26 : 10:32:43

Can you post table structure, sample data and expected out put.

Please help us to help you?

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

barflyz
Starting Member

47 Posts

Posted - 2010-04-26 : 10:47:58

I can in a couple of days as the Server is down for maint. thanks so much

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-27 : 00:24:00

is the above shown sample columns in your table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

barflyz
Starting Member

47 Posts

Posted - 2010-04-27 : 12:28:34

yes visakh16, I know there must be an easier way than making a table for each recent date and then performing a not in using max for the 2nd etc...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources