Post reply

Selecting Max Date

  • October 4, 2011 at 4:38 pm

    #258487

    Hello All,

    I have a table that consist of data like this:

    Table Name:Servers

    Server_nameServer_statusUpdate_Date

    Server1prod2009-10-22

    Server1decom2009-10-01

    Server2decom2010-01-18

    Server3decom2007-07-25

    Server3prod2007-06-19

    Server3test2007-04-10

    Server4decom2010-02-02

    Server4prod2010-01-31

    My goal is to query for all decom servers. If there are multiple server with the same server name with a server status of decom, I need to verify the Update Date and only select that record if the date is later than the other dates for that same server name.

    I tried using max(Update_Date) but it returns the max date for all entries.

    select

    srv.server_name

    ,srv.SERVER_STATUS

    ,srv.UPDATE_DATE

    from [WITT Info].dbo.[SERVERS] srv

    WHERE srv.UPDATE_DATE = (SELECT MAX(srv.UPDATE_DATE) from [WITT Info].dbo.[SERVERS] srv)

    Any suggestions on how I might accomplish this?

    Thanks in advance

  • October 4, 2011 at 5:04 pm

    #1390453

    Please try it below:

    select

    srv.server_name

    ,srv.SERVER_STATUS

    ,srv.UPDATE_DATE

    ,(SELECT MAX(srv.UPDATE_DATE) from [WITT Info].dbo.[SERVERS] Srv2 where srv2.server_name =srv.server_name )

    from [WITT Info].dbo.[SERVERS] srv

    group by srv.server_name ,srv.SERVER_STATUS,srv.UPDATE_DATE

    The first part of the entire solution..But, the second part how is to ensure the optimal performance particularly more huge data entity…?

    This could be done by creating the below 2 covering compound index :

    Create nonclustered index SERVERS_index1 on SERVERS

    (server_name asc)

    include

    (

    UPDATE_DATE )

    Create nonclustered index SERVERS_index2 on SERVERS

    (server_name asc)

    include

    (

    ,SERVER_STATUS

    ,UPDATE_DATE )

    [font="Arial Black"]Performance Guard[/font]

    [font="Courier New"]Just ask me to get 0 sec for all queries[/font]

  • October 5, 2011 at 3:56 am

    #1390560

    I would rather try something like below query. It uses window function row_number() to partition by server - status combination, ordering descending on the update_date. All most recent server - status combinations now have 1 in their number, so it is easy to get just those most recent rows from the set.

    You provided us no DDL nor sample data, so this is untested. Please read the link 'Posting Data Etiquette' in my footer text for information on how and WHY you should provide these.

    select srv.server_name,

    srv.SERVER_STATUS,

    srv.UPDATE_DATE

    from (

    select srv.server_name,

    srv.SERVER_STATUS,

    srv.UPDATE_DATE,

    row_number() over (partition by srv.server_name, srv.SERVER_STATUS order by srv.UPDATE_DATE desc) as nr

    from [WITT Info].dbo.[SERVERS] srv

    ) t

    where t.SERVER_STATUS = 'decom'

    and t.nr = 1


    Posting Data Etiquette - Jeff Moden[/url]
    Posting Performance Based Questions - Gail Shaw[/url]
    Hidden RBAR - Jeff Moden[/url]
    Cross Tabs and Pivots - Jeff Moden[/url]
    Catch-all queries - Gail Shaw[/url]

    If you don't have time to do it right, when will you have time to do it over?

  • October 5, 2011 at 8:35 pm

    #1391056

    Thank you all for your input on this and my apologies for my poor "Posting Data Etiquette" - another learning experience.

    Just as an fyi - all three suggestions worked fine for what I was trying to do.

    Again, I really appreciate the help!!

    Ronnie

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply