Explain mysql queries

Question

I have a query that I wonder if the result that I get is the one that I expect.

The structure of the table is as follows:

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 a way to show only the latest error on each platform?

in the case above

Result:

  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 setback on the platform. It relies on id be consistent in time.

Replace * columns you really 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 Left Join to get only the rows with the highest 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 request

Answer 3

This should do the trick, I'm not sure if it will be effective:

 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 save the identifier from the testcases table in the TestCase column in the results table instead of "TestCase". Depending on the type of data in this column, you may get slightly better performance.