locked
SQL Get the latest date in query... RRS feed

  • Question

  • User-507786106 posted

    I have to loop through the database and get author records, some authors wrote more than one blog, I need only one record per author, but the latest blog written.

    Please help me construct an SQL statement that select all records, one per person and the latest date.

    thank you;.

    Tuesday, May 7, 2019 6:03 PM

Answers

  • User77042963 posted
    Select FirstName, LastName, BlogDate, BookTitle, Genre from (
    
    SELECT FirstName, LastName, BlogDate, BookTitle, Genre 
    ,row_number() over(partition by FirstName, LastName order by BlogDate DESC) rn 
    FROM dbo.AuthorBlog
    
    ) t
    
    WHERE rn=1

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 7, 2019 7:06 PM

All replies

  • User77042963 posted

    You are not looping things in your query.

    Without table information, I can give you some sample only:

    Select personid, pdate from (

    Select personid, pdate, row_number() over(partition by persionid order by pdate DESC) rn from yourtable

    ) t

    WHERE rn=1

    Tuesday, May 7, 2019 6:28 PM
  • User-507786106 posted

    Thank you,  Here is my table, this will help me follow you better.

    SELECT FirstName, LastName, BlogDate, BookTitle, Genre FROM dbo.AuthorBlog.

    The author may have one or more BlogDate and I need to get the latest BlogDate.  I should return 200 records but each will have different author and blogdate.

    Example, dates

    ... 2007-02-07  <-- I will need this record...
    ... 2005-09-12 

    Tuesday, May 7, 2019 6:32 PM
  • User77042963 posted
    Select FirstName, LastName, BlogDate, BookTitle, Genre from (
    
    SELECT FirstName, LastName, BlogDate, BookTitle, Genre 
    ,row_number() over(partition by FirstName, LastName order by BlogDate DESC) rn 
    FROM dbo.AuthorBlog
    
    ) t
    
    WHERE rn=1

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 7, 2019 7:06 PM