January 24, 2006 at 9:33 am
data set up
CREATE TABLE [dbo].[rats_server] (
[server_id] [int] IDENTITY (1, 1) NOT NULL ,
[server_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[systems_status] (
[server_id] [int] IDENTITY (1, 1) NOT NULL ,
[server_detail_id] [int] NOT NULL ,
[server_sts] [int] NOT NULL ,
[server_location] [bit] NOT NULL ,
[server_info] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[who_updated] [bigint] NULL ,
[last_updated] [datetime] NULL
) ON [PRIMARY]
server_detail_id is the id from rats_server
I need a query that will give me just one entry in the systems_status table for each server in the rats_server table
here is some sample data
rats_server
server_id - server_name
1 - MIDCAD
2 - CTS
3 - X-Gate
systems_status
server_id - server_detail_id - server_sts - server_location - server_info - who_updated - last_updated
1 - 1 - 0 - 1 - Normal all running okay this morning - 2 - 11-16-05
2 - 1 - 0 - 1 - Normal check this day - 2 - 12-16-05
3 - 1 - 0 - 1 - Normal all doing fine - 2 - 1-16-06
4 - 2 - 0 - 1 - Normal everything okay - 2 - 11-16-05
5 - 2 - 0 - 1 - Running off backup - 2 - 12-16-05
6 - 2 - 0 - 1 - Normal back on prime - 2 - 1-16-06
7 - 3 - 0 - 1 - Normal running on Prome - 2 - 11-16-05
So now I only need to pull records 3, 6 and 7
Help
I have tried multiple ways and am unable to make it work
January 24, 2006 at 10:03 am
You typically do this by joining to a derived table that generates the Max() of each group for joining to. In your case, you need a derived table of Max(last_updated) for each server_detail_id.
SELECT ss.*
FROM systems_status As ss
INNER JOIN
(
SELECT server_detail_id, Max(last_updated) As MostRecentUpdate
FROM systems_status
GROUP BY server_detail_id
) dtRecent -- A derived table, we're calling it "dtRecent"
On (dtRecent.server_detail_id = ss.server_detail_id AND
dtRecent.MostRecentUpdate = ss.last_updated)
January 24, 2006 at 10:34 am
Thank you very much - I would have never come up with that
thank you again
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply