Select all last results from a range of hosts

[zaradell]

Good afternoon

I'm trying to select all the last results from a range of hosts that are performing tests, and their results are written in one table.

For instance, I have a table with 3 users and the fields:

ID Timeofexecution Result
UserA 2013-03-10 13
UserA 2013-03-09 9
UserA 2013-03-08 54
UserB 2013-03-12 1
UserB 2013-03-11 19
UserC 2013-03-11 132

I'm trying to create a query that returns me the last results from the 3 users.

Expected output:

ID ||| Timeofexecution ||| Result
UserA ||| 2013-03-10 ||| 13
UserB ||| 2013-03-12 1
UserC ||| 2013-03-11 132

+-----------------+------------------------+
| identifierValue | max(time_of_execution) |
+-----------------+------------------------+
| X-SONDA44 | 2013-06-12 19:21:36 |
+-----------------+------------------------+


I´ve created the query that returns me the last result filtered by timeofexecution:

select id,max(time_of_execution) from results_tbl where ID LIKE '%User%';

but it only returns me the last entry in the table, and not the last entries of each user.

Please help


Best wishes

 

[r937]

SELECT t.id
     , t.time_of_execution
     , t.result
  FROM ( SELECT id
              , MAX(time_of_execution) AS max_time
           FROM results_tbl 
         GROUP
             BY id ) AS m
INNER
  JOIN results_tbl AS t
    ON t.id = m.id
   AND t.time_of_execution = t.max_time

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[zaradell]

r937

Thank you for your feedback. It worked perfectly!

Just one final question. How do you adapt your query to extract the AVG result? I want to know the AVG value of the last results between the 3 users.

For instance, right now I´m able to extract the results as expected:

ID ||| Timeofexecution ||| Result
UserA ||| 2013-03-10 ||| 5
UserB ||| 2013-03-12 ||| 10
UserC ||| 2013-03-11 ||| 20

In the example above, it should return the value 8,75 (AVG between the last results 5, 10 and 20)

 

[r937]

SELECT AVG(t.result) AS avg_result
  FROM ( SELECT id
              , MAX(time_of_execution) AS max_time
           FROM results_tbl 
         GROUP
             BY id ) AS m
INNER
  JOIN results_tbl AS t
    ON t.id = m.id
   AND t.time_of_execution = t.max_time

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[zaradell]

r937

You´re a life saver

Working like a charm

Thank you very much

 

[zaradell]

Ok

One more, and this is a little trickier, not sure if it is even possible:

Instead of retrieving the AVG of only the last results, the query should extract the AVG result of all the results based on an unique test ID?

For instance, I have several results identified on an unique test ID with the same 3 users:

TestID ||| User ||| Timeofexecution ||| Result
1245 ||| UserA ||| 2013-03-10 ||| 5
1245 ||| UserB ||| 2013-03-12 ||| 10
1245 ||| UserC ||| 2013-03-11 ||| 20
3286 ||| UserA ||| 2013-04-10 ||| 60
3286 ||| UserB ||| 2013-04-12 ||| 30
3286 ||| UserC ||| 2013-04-11 ||| 50
5286 ||| UserA ||| 2013-05-10 ||| 30
5286 ||| UserB ||| 2013-05-12 ||| 40
5286 ||| UserC ||| 2013-05-11 ||| 30


In the example above, the query should return 3 entries whith 3 fields based on the correspondent ID test (where the field Timeofexecution should be the MAX value of all the dates of that testID):

TestID ||| AVG_Result ||| Timeofexecution
1245 ||| 8.75 ||| 2013-03-12
3286 ||| 46.6 ||| 2013-04-12
5286 ||| 33.3 ||| 2013-05-12

 

[zaradell]

Actually my problem is a little more complex that I´ve exposed before.

I´ve confirmed, and I have no unique ID per test, but instead I have a unique ID per test/per user. Something in these lines:

TestID ||| User ||| Timeofexecution ||| Result
1245 ||| UserA ||| 2013-03-10 09h50 ||| 5
1248 ||| UserB ||| 2013-03-10 09h10 ||| 10
1394 ||| UserC ||| 2013-03-10 09h40 ||| 20
3288 ||| UserA ||| 2013-03-10 10h10 ||| 60
3195 ||| UserB ||| 2013-03-10 10h30 ||| 30
4721 ||| UserC ||| 2013-03-10 10h45 ||| 50
5276 ||| UserA ||| 2013-03-10 11h20 ||| 30
5214 ||| UserB ||| 2013-03-10 11h40 ||| 40
6257 ||| UserC ||| 2013-03-10 11h50 ||| 30

So instead of grouping the AVG results per test ID, is it possible somehow to group them by the hour the tests were run? In the example above, the objective is getting the AVG value of all the results grouped by the hour they were run:

Timeofexecution ||| AVG_Result
2013-03-10 09h00 ||| 8.75
2013-03-10 10h00 ||| 46.6
2013-03-10 11h00 ||| 33.3

 

[zaradell]

Ok, I figured it out

I got it working with the following query

select timeofexecution , result from results_tbl GROUP BY DATE( timeofexecution ), HOUR( timeofexecution );