[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 176
  • Last Modified:

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
0
mickinoz2005
Asked:
mickinoz2005
  • 2
2 Solutions
 
ajitha75Commented:
something like this??

select s2.eventtime, s2.description, s2.servername from (select servername, max(eventtime) from sampletable group by servername) as S1
inner join servername s2 where s1.servername = s2.servername and s1.eventtime = s2.eventtime

-Ajitha
0
 
SharathData EngineerCommented:
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

0
 
SharathData EngineerCommented:
This is another way which works in 2000 also.

select *
  from YourTable t1
 where t1.Event_Time = (select max(t2.Event_Time) from YourTable as t2 where t1.ServerName = t2.ServerName)

Open in new window

0
 
mickinoz2005Author Commented:
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
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now