Link to home
Start Free TrialLog in
Avatar of mickinoz2005
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
ASKER CERTIFIED SOLUTION
Avatar of ajitha75
ajitha75
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Sharath S
If you are in 2005, you can try like this.

select Event_Time,Description,ServerName
  from (select Event_Time,Description,ServerName,
               row_number() over (partition by ServerName order by Event_Time desc) rn
          from YourTable) as t1
 where rn = 1

Open in new window

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mickinoz2005
mickinoz2005

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