Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2011
    Location
    Corpus Christi, TX
    Posts
    26

    Unanswered: Query to Select Lastest Record

    If I selected the data below, how could I write a query to only keep the latest record. I added a "FileDate" column to label the last record (FileDate 200807)I want to display.

    LeaseID OpID Year Month MCF FileDate
    208801 40798 2006 10 7660 200807
    208801 40798 2006 10 7680 200801
    208801 40798 2006 10 7784 200704

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I'm not really sure what you mean by "last record (FileDate 200807)". Depending on your answer, you should use ASC or DESC.
    Code:
    SELECT TOP 1 LeaseID, OpID, Year, Month, MCF, FileDate
    FROM MyTable
    ORDER BY FileDate (ASC/DESC)
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Oct 2011
    Location
    Corpus Christi, TX
    Posts
    26
    The FileDate is used to label the last record coming into the database. Each month we have a lot of duplicate records coming into the table. If the LeaseID,Year, and Month are the same, I need to go with the last that was load. It would be the record with the FileDate of 200807.

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    OK, With "last" you mean the most recently added record. Then you should use DESC.

    But I think what you really need is this:
    Code:
    WITH CTE AS
    (SELECT LeaseID, OpID, Year, Month, MCF, FileDate, 
       ROW_NUMBER() OVER (PARTITION BY LeaseID, OpID, Year, Month ORDER BY FileDate DESC) AS RowNum
    FROM MyTable)
    SELECT *
    FROM CTE
    WHERE RowNum = 1
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Oct 2011
    Location
    Corpus Christi, TX
    Posts
    26
    Yes, that is exactly the result I am looking for. I'm testing the records right now and everything looks good. Thanks! You are literally a life saver.

  6. #6
    Join Date
    Oct 2011
    Location
    Corpus Christi, TX
    Posts
    26
    One more question. How would I create a new table with these results?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •