distinct record set

  • 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

  • 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)

  • 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