Sql Query to find latest score using Max of 2 Dates

[TennisNut]

Hello All,

I am trying to write a SQL Query against a DB2 database to find the latest 2 scores for each individual. (I know this is a SQL Server forum, but the DB2 forum did not have many posts regarding SQL Queries).
To simplify:
Table1 has indiv#, scrid#, datetime1, score1
Table2 has scrid#, datetime2, score2

I am using a left outer join on scrid# from Table1 to Table2, since a record always has data in Table1, but not always in Table2.

Result: I want the latest 2 scores for each individual...1st by the latest datetime1 and then latest datetime2. If a record does NOT have any data in Table2, then the score needed would be score1 with the latest datetime1. If a record does have a score2, then the score will always be score2 with max(datetime1) 1st then max(datetime2).

Example1
indiv# datetime1 score1 datetime2 score2
1 02-15-07 08:55 760 02-15-07 08:54 775
1 02-15-07 08:55 760 02-15-07 08:55 780
1 09-01-05 16:10 550

Example2
indiv# datetime1 score1 datetime2 score2
2 03-08-10 12:55 688 03-08-10 12:55 670
2 03-08-10 18:01 815
2 12-15-08 09:11 500

Example3
indiv# datetime1 score1 datetime2 score2
3 05-21-04 07:12 714 05-21-04 02:00 1
3 05-21-04 07:14 700 05-21-04 02:00 0
3 10-30-02 23:45 500

For these examples, output would be:
indiv# topscore1 topscore2
1 780 775
2 815 670
3 0 1

I would like the query to be efficient when it comes to hitting the database.

TIA,
JSR

 

[markros]

In SQL Server 2005 and up you can try

;with cte as (select ID, case when(DateTime2 IS NULL then Score1 else Score2 end) as Score, row_number() over (partition by ID order by Datetime1 DESC, DateTime2 DESC) as Row from myResult)

select ID,[1] as TopScore1, [2] as TopScore2 from
cte PIVOT (max(Score) for Row in ([1],[2])) pvt

I doubt it's easy to adapt in DB2, though.

PluralSight Learning Library

 

[TennisNut]

markros,

thanks for the quick reply...unfortunatley, I am unable to use PIVOT.

 

[markros]

Use case based pivot. Are you able to work out the first part of the query? Cte?

select ID, max(case when Row = 1 then Score end) as TopScore1,
max(case when Row = 2 then Score end) as TopScore2 from cte
group by ID

PluralSight Learning Library