INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Select all last results from a range of hosts

Select all last results from a range of hosts

(OP)
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

RE: Select all last results from a range of hosts

CODE

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

RE: Select all last results from a range of hosts

(OP)
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)

RE: Select all last results from a range of hosts

CODE

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

RE: Select all last results from a range of hosts

(OP)
r937

You´re a life saver

Working like a charm

Thank you very much

RE: Select all last results from a range of hosts

(OP)
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

RE: Select all last results from a range of hosts

(OP)
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




RE: Select all last results from a range of hosts

(OP)
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 );


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close