I have a a table named titles
. Each title can have multiple authors so I store the author ids and the corresponding title ids in look up table: lu_titles_authors
I want to display a list of the newest titles on a webpage but also make sure that each author gets shown only once in this row of titles and MOST IMPORTANTLY that the selected titles for each author is the title with the highest/newest date.
I started with:
SELECT * FROM titels INNER JOIN lu_titles_authors ON T_Id = LUTITAU_T_Id ORDER BY T_Date DESC
This gets me half way.
Then I made in to:
SELECT * FROM titels INNER JOIN lu_titles_authors ON T_Id = LUTITAU_T_Id GROUP BY LUTITAU_A_Id ORDER BY T_Date DESC
This does seem to filter out duplicate authors in the list of results, but the chosen titles that remain are not the latest titles of this author...
Can someone tell me how I need to change the query in order to achieve this?
If anything is not clear enough let me know and I will try to explain it better...
I want to query one title of each author and the title needs to be the one with the newest/highest date...
Thank you very much for any replies!