locked
How can i get the latest record for each server RRS feed

  • Question

  • I have a table which holds daily values of the size of my database servers and the databases that they all hold, I now want to create a view which will show only the most recent entry for every server.

    The query below returns everything, the recorddate shows the date that the statistics were collected, so this table holds thousands of rows for daily values of the sizes of my databases, but the objective is that I want to only return the values for today only within a view and not pass any parameter, if i used a between clause, the problem is that the statistics may be collected many times a day but i am only interested in the latest record per server.

    select Servername, RecordDate, Name, SpaceAllocated, Filename, Free_Space_MB
    from tblDBSpaceUsed

    Tuesday, July 6, 2010 4:27 PM

Answers

  • I altered the code and then used the below, this made sure that records returned were based both on the server and filename and also the max record for the entry.

     

    SELECT
     Servername
    ,RecordDate
    ,Name
    ,SpaceAllocated
    ,Filename
    ,Free_Space_MB
    FROM (
    select Servername
    ,RecordDate
    ,Name
    ,SpaceAllocated
    ,Filename
    ,Free_Space_MB
    ,ROW_NUMBER () OVER (PARTITION BY ServerName,Filename
    ORDER BY RecordDate DESC ) AS Row
    from tblDBSpaceUsed) X
    WHERE Row = 1
    • Marked as answer by Kalman Toth Monday, July 12, 2010 4:45 PM
    Wednesday, July 7, 2010 2:06 PM

All replies

  • Hi,

    You can try with the following Select query.

    select Servername, RecordDate, Name, SpaceAllocated, Filename, Free_Space_MB
    from tblDBSpaceUsed
    where RecordDate = getdate()

    Tuesday, July 6, 2010 4:34 PM
  • Here is one way

    SELECT Servername
    		,RecordDate
    		,Name
    		,SpaceAllocated
    		,Filename
    		,Free_Space_MB
    FROM(
    select Servername
    		,RecordDate
    		,Name
    		,SpaceAllocated
    		,Filename
    		,Free_Space_MB
    		,ROW_NUMBER() OVER(PARTITION BY ServerName ORDER BY RecordDate DESC) AS Row
    from tblDBSpaceUsed) X
    WHERE Row = 1

    Abdallah El-Chal, PMP, ITIL, MCTS
    Tuesday, July 6, 2010 4:34 PM
  • One of possible solutions:

    select Servername, RecordDate, Name, 
    SpaceAllocated, Filename, Free_Space_MB from
    (select Servername, RecordDate, Name, 
    SpaceAllocated, Filename, Free_Space_MB, row_number() over (partition by ServerName order by [RecordDate] DESC) as Row from 
    from tblDBSpaceUsed) X where Row = 1


    For various solutions of this problem please take a moment to look at these two blogs:

    Including an Aggregated Column's Related Values
    Including an Aggregated Column's Related Values - Part 2


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Edited by Naomi N Wednesday, July 7, 2010 2:20 PM
    Tuesday, July 6, 2010 4:37 PM
  • thanks Guys

    when i tried the query using ROW_NUMBER FUNCTION, i still wasn't getting the right results as f i also select out the ROW column, i was expecting to see a lot of entries returning 1, but i only get one entry per server.

    Wednesday, July 7, 2010 10:22 AM
  • Use RANK instead of Row_number.
    Wednesday, July 7, 2010 10:25 AM
  • You can use RANK or my suggesstion T-SQL DENSE_RANK function to get all rows having same value
    SQL Server and T-SQL Tutorials
    My Personal Site
    Our true mentor in life is science
    Wednesday, July 7, 2010 12:01 PM
  • thanks Guys

    when i tried the query using ROW_NUMBER FUNCTION, i still wasn't getting the right results as f i also select out the ROW column, i was expecting to see a lot of entries returning 1, but i only get one entry per server.


    Wasn't that your requirement? "How can i get the latest record for each server"

     If not, please post some test data.


    Abdallah El-Chal, PMP, ITIL, MCTS
    Wednesday, July 7, 2010 12:30 PM
  • I altered the code and then used the below, this made sure that records returned were based both on the server and filename and also the max record for the entry.

     

    SELECT
     Servername
    ,RecordDate
    ,Name
    ,SpaceAllocated
    ,Filename
    ,Free_Space_MB
    FROM (
    select Servername
    ,RecordDate
    ,Name
    ,SpaceAllocated
    ,Filename
    ,Free_Space_MB
    ,ROW_NUMBER () OVER (PARTITION BY ServerName,Filename
    ORDER BY RecordDate DESC ) AS Row
    from tblDBSpaceUsed) X
    WHERE Row = 1
    • Marked as answer by Kalman Toth Monday, July 12, 2010 4:45 PM
    Wednesday, July 7, 2010 2:06 PM