Postgresql查询问题

Question

so I have a problem that I'm trying to resolve since a couple of weeks, but I'm not coming to any solution. So here are the tables that I'm trying to make a query on: Tables

I obviously joined them (easy):

SELECT a.date,b.title,b.author,u.nick
FROM book_add a 
INNER JOIN user u on(a.user_fk=u.id) 
INNER JOIN book b on (a.book_fk=b.id) 
INNER JOIN status s ON(a.status_fk=s.id) 
WHERE s.description='active';

Now here comes the problem: I want to order the rows by date desc and distinct them, so that the last inserted row (with the newest date) the first row is. But results are very odd once they get distincted. I tried this:

SELECT a.date,b.title,b.author,u.nick
FROM book_add a 
INNER JOIN user u on(a.user_fk=u.id) 
INNER JOIN book b on (a.book_fk=b.id) 
INNER JOIN status s ON(a.status_fk=s.id) 
WHERE s.description='active' ORDER BY a.date DESC;

this works, though once i try distinctig a.book_fk results are wrong:

SELECT DISTINCT ON(a.book_fk)a.book_fk,a.date,b.title,b.author,u.nick
FROM book_add a 
INNER JOIN user u on(a.user_fk=u.id) 
INNER JOIN book b on (a.book_fk=b.id) 
INNER JOIN status s ON(a.status_fk=s.id) 
WHERE s.description='active' ORDER BY a.date DESC;

I even tried approaches like this one, but without success:

SELECT * FROM (SELECT DISTINCT  
ON(a.book_fk)a.book_fk,a.date,b.title,b.author,u.nick
FROM book_add a
INNER JOIN user u on(a.user_fk=u.id) 
INNER JOIN book b on (a.book_fk=b.id) 
INNER JOIN status s ON(a.status_fk=s.id) 
WHERE s.description='active') res ORDER BY res.date DESC

Could someone help me? I would be very happy! Thank you!

Answer 1

It sounds like you're trying to get one row per book, with the most recent user/status? In that case this should work:

SELECT DISTINCT ON(a.book_fk)
       a.book_fk, a.date, b.title, b.author, u.nick
FROM   book_add a 
INNER JOIN user u on(a.user_fk=u.id) 
INNER JOIN book b on (a.book_fk=b.id) 
INNER JOIN status s ON(a.status_fk=s.id) 
WHERE  s.description='active'
ORDER BY a.book_fk, a.date DESC
;