CODEWITHSUNDEEP
mysqlsql
user2532770
user2532770

Reputation: 91

sql return only max date for each id

I have a database with one table that looks like this

Books(id, title, author_id, date)

I am using type DATE for the date field.

What the query is looking to do is return only the most recent book from each author, so even for the books that have the same author_id, only return the book that has the most recent date field for that author. So for all books find the most recent book from each author, returning title, author_id, and date.

I believe I will need to use the MAX function with a GROUP BY author_id....or perhaps include a HAVING max(date)? Sort of a side note is it possible when returning the date to have it be age in years it has been released up to 1 decimal point? (ex. 3.1 if book date was just over 3 years old?).

Upvotes: 3

Views: 5138

Answers (6)

SQLiscious
SQLiscious

Reputation: 54

       Select * from
            (
                Select
                    books.*,
                    Row_Number() OVER (PARTITION BY author_id ORDER BY date desc ) AS row_num
                from books
            ) books
        WHERE row_num = 1

Upvotes: 0

Vivek Sadh
Vivek Sadh

Reputation: 4268

Use this:-

SELECT id,title,author_id,date
FROM Books t1
WHERE t1.id = (SELECT t2.id
              FROM Books t2
              WHERE t2.author_id = t1.author_id
              ORDER BY t2.date desc
              LIMIT 1)

This query will return the most recent book of each author.

Check this:- SQL FIDDLE

Upvotes: 1

T I
T I

Reputation: 9933

SELECT b1.*
FROM Books b1
JOIN (
    SELECT author_id, MAX(date) date
    FROM Books
    GROUP BY author_id
) b2
  ON b2.author_id = b1.author_id
 AND b2.date = b1.date

demo

Upvotes: 1

SylvainL
SylvainL

Reputation: 3938

On SQL-Server, I would write something like this to find the most recent date for each author:

Select author_id, Max(date) From Books Group By author_id

You can then use a modification of this as a subquery to find the most recent book for each author:

Select B.* from Books as B
where B.date = (Select Max(date) From Books B2 where B2.author_id = B.author_id)

For MySQL, I'm not sure if this will work but probably that it will. Of course, there are other ways of doing this.

Upvotes: 0

Justin
Justin

Reputation: 9724

Query:

SELECT b.*
FROM Books b
WHERE b.id = (SELECT b2.id
              FROM Books b2
              WHERE b2.author_id = b.author_id
              ORDER BY b2.date desc
              LIMIT 1)

Upvotes: 1

fthiella
fthiella

Reputation: 49049

You can use this query:

SELECT *
FROM Books
WHERE (author_id, date) IN (SELECT author_id, MAX(date)
                            FROM Books
                            GROUP BY author_id)

subquery will return, for every author, the maximum date of a book. Outer query will return all books for every author with the maximum date.

Please notice that this could return more than one book for every author if they are published in the same maximum date.

Upvotes: 5

Related Questions