Post reply

select the row having the latest backup date

  • November 29, 2014 at 9:05 am

    #397708

    Hi everyone,

    I have a very simple question.

    I have a select query that results all the dbs and their several backup dates&times. Howover I only need the latest backup dates for each of the db.

    How can I write that in tsql?

  • November 29, 2014 at 9:13 am

    #1761516

    Select DbName, LatestBackupDate = Max(BackupDate)

    from table

    group by DbName

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • December 1, 2014 at 3:41 am

    #1761637

    Hi,

    This is what I get when I run the script.

    Column 'xxxx.dbo.backup_database_daily.backupType' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

  • December 1, 2014 at 3:48 am

    #1761638

    Are you looking for separate rows for each backup type for each database? If so, just add backup type to your SELECT list and GROUP clause. If , on the other hand, you just want the last backup of any type, you'll need to join the result set of Phil's query back to the original table in order to get other columns.

    John

  • December 1, 2014 at 6:10 am

    #1761679

    I mean this. Assume I have 2 backup dates for hostname "c" and 3 backup dates for hostname "d". And the table is like that:

    hostname last_backup

    c 2014-11-29 10:16

    c 2014-11-29 12:16

    d 2014-11-29 11:16

    d 2014-11-29 12:16

    d 2014-11-29 13:16

    The resulting table should be like this:

    hostname last_backup

    c 2014-11-29 12:16

    d 2014-11-29 13:16

  • December 1, 2014 at 6:26 am

    #1761684

    princessthedba (12/1/2014)

    I mean this. Assume I have 2 backup dates for hostname "c" and 3 backup dates for hostname "d". And the table is like that:

    hostname last_backup

    c 2014-11-29 10:16

    c 2014-11-29 12:16

    d 2014-11-29 11:16

    d 2014-11-29 12:16

    d 2014-11-29 13:16

    The resulting table should be like this:

    hostname last_backup

    c 2014-11-29 12:16

    d 2014-11-29 13:16

    Your sample data does not even include database name. Are you sure that you are providing us with all the info we need?

    For your example

    select hostname, max(last_backup)

    from table

    group by hostname

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

Viewing 6 posts - 1 through 5 (of 5 total)

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