mickinoz2005
asked on
SQL Query Help
Hi all,
I have a table and need to do a query on it - table sample is as follows
EVENT TIME DESCRIPTION SERVERNAME
2010-01-07 18:07:45.000 AV Status: Up-to-date 07/01/2010 18:04:29 tpdbctx02
2010-01-07 19:14:22.000 AV Status: Up-to-date 07/01/2010 19:04:29 tpdbctx02
2010-01-07 18:07:45.000 AV Status: Up-to-date 07/01/2010 17:45:57 tpdbdc1
2010-01-07 19:14:23.000 AV Status: Up-to-date 07/01/2010 18:45:55 tpdbdc1
2010-01-07 18:07:45.000 AV Status: Up-to-date 07/01/2010 17:50:22 tpdbdc3
2010-01-07 19:14:23.000 AV Status: Up-to-date 07/01/2010 18:50:23 tpdbdc3
2010-01-07 18:07:45.000 AV Status: Up-to-date 07/01/2010 17:49:38 tpdbex1
2010-01-07 19:14:25.000 AV Status: Up-to-date 07/01/2010 18:49:45 tpdbex1
2010-01-07 18:07:45.000 AV Status: Up-to-date 07/01/2010 17:48:19 tpdbsql02
2010-01-07 19:14:24.000 AV Status: Up-to-date 07/01/2010 18:48:30 tpdbsql02
2010-01-07 18:07:45.000 AV Status: Up-to-date 07/01/2010 17:53:59 server
2010-01-07 19:14:21.000 AV Status: Up-to-date 07/01/2010 18:54:03 server
Basically what I want to do is run a query on this table and always get the last Eventtime / description for each server
so as you can see there is currently two entries for each server one at 18:07 and one at 19:14 I want the one at 19:14 for each server.
Sample output
2010-01-07 19:14:22.000 AV Status: Up-to-date 07/01/2010 19:04:29 tpdbctx02
2010-01-07 19:14:23.000 AV Status: Up-to-date 07/01/2010 18:45:55 tpdbdc1
2010-01-07 19:14:23.000 AV Status: Up-to-date 07/01/2010 18:50:23 tpdbdc3
Every day there will automatically be a new entry added so i want to make sure I get the newest every time.
I have tried using MAX but not working for me as I need to do a group by and can only do it with one column.
How can I do this in SQL please
Thanks
I have a table and need to do a query on it - table sample is as follows
EVENT TIME DESCRIPTION SERVERNAME
2010-01-07 18:07:45.000 AV Status: Up-to-date 07/01/2010 18:04:29 tpdbctx02
2010-01-07 19:14:22.000 AV Status: Up-to-date 07/01/2010 19:04:29 tpdbctx02
2010-01-07 18:07:45.000 AV Status: Up-to-date 07/01/2010 17:45:57 tpdbdc1
2010-01-07 19:14:23.000 AV Status: Up-to-date 07/01/2010 18:45:55 tpdbdc1
2010-01-07 18:07:45.000 AV Status: Up-to-date 07/01/2010 17:50:22 tpdbdc3
2010-01-07 19:14:23.000 AV Status: Up-to-date 07/01/2010 18:50:23 tpdbdc3
2010-01-07 18:07:45.000 AV Status: Up-to-date 07/01/2010 17:49:38 tpdbex1
2010-01-07 19:14:25.000 AV Status: Up-to-date 07/01/2010 18:49:45 tpdbex1
2010-01-07 18:07:45.000 AV Status: Up-to-date 07/01/2010 17:48:19 tpdbsql02
2010-01-07 19:14:24.000 AV Status: Up-to-date 07/01/2010 18:48:30 tpdbsql02
2010-01-07 18:07:45.000 AV Status: Up-to-date 07/01/2010 17:53:59 server
2010-01-07 19:14:21.000 AV Status: Up-to-date 07/01/2010 18:54:03 server
Basically what I want to do is run a query on this table and always get the last Eventtime / description for each server
so as you can see there is currently two entries for each server one at 18:07 and one at 19:14 I want the one at 19:14 for each server.
Sample output
2010-01-07 19:14:22.000 AV Status: Up-to-date 07/01/2010 19:04:29 tpdbctx02
2010-01-07 19:14:23.000 AV Status: Up-to-date 07/01/2010 18:45:55 tpdbdc1
2010-01-07 19:14:23.000 AV Status: Up-to-date 07/01/2010 18:50:23 tpdbdc3
Every day there will automatically be a new entry added so i want to make sure I get the newest every time.
I have tried using MAX but not working for me as I need to do a group by and can only do it with one column.
How can I do this in SQL please
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi folks,
Thanks for your input - I ended up not using any of your solutions and just worked out a way myself but I do appreciate the comments and the input.
Thanks
Thanks for your input - I ended up not using any of your solutions and just worked out a way myself but I do appreciate the comments and the input.
Thanks
Open in new window