mysql query clarification

Question

I have a query which I am wondering if the result I am getting is the one that I am expecting.

The table structure goes like this :

Table :  results     
ID TestCase Set  Analyzed Verdict StartTime             Platform
1  1010101  ros2 false    fail    18/04/2010 20:23:44   P1
2  1010101  ros3 false    fail    19/04/2010 22:22:33   P1
3  1232323  ros2 true     pass    19/04/2010 22:22:33   P1
4  1232323  ros3 false    fail    29/04/2010 22:22:33   P2

Table : testcases
ID TestCase type
1  1010101  NOSNOS
2  1232323  N212NS

is there any way to display only the latest fails on each platform?

in the above case

Result shoud be :

 ID TestCase Set  Analyzed Verdict StartTime             Platform type 
 2  1010101  ros3 false    fail    19/04/2010 22:22:33   P1       NOSNOS 
 4  1232323  ros3 false    fail    29/04/2010 22:22:33   P2       N212NS

Answer 1

This should give you the latest fails per platform. It relies on ids being sequential by time.

Replace the * by the columns you actually need.

Select *
From results r
Join testcases t On ( t.testCase = r.testCase )
Where r.id In (
    Select Max(id)
    From results
    Where verdict = 'fail'
    Group By platform
)

---

Alternatively you can use a Left Join to get only the rows with greatest startTime per platform:

Select *
From results r
Join testcases t On ( t.testCase = r.testCase )
Left Join results r2 On (     r2.platform = r.platform
                          And r2.verdict = r.verdict
                          And r2.startTime > r.startTime )
Where r.verdict = 'fail'
  And r2.id Is Null

Answer 2

Yes, Use group by Testcase and order by ID desc limit 1 with your query

Answer 3

This should do the trick, I'm not sure if it's going to be efficient:

select r.*, t.type
from
(
Select TestCase, max(StartTime) lastDate
From results
group by TestCase
)big
inner join results r on r.StartTime = big.lastDate and r.TestCase = big.TestCase
inner join testcases t on t.TestCase = r.TestCase

I' suggest you store the ID from testcases table in TestCase column on results table, instead of the 'TestCase'. Depending on the data type on this column, you might get a little better performance.