question

Mhlengi avatar image
Mhlengi asked

Query to return the items that meet the criteria for being the latest.

I want to return the last four issues of a book (I have circled the ones in question) dynamically (not hard coded). For example if the new book called SHBb19 is released then SHBb18 must be not displayed on the list. I need this to always display last four issues of each book. Thank you
sqlquerydatabasetop
capture.png (12.1 KiB)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
You could either do a rank of the book-issue, ordered by the issue in descending order, and then pick those with rank=1. WITH CTE AS ( SELECT BookIssue, RANK() OVER(ORDER BY BookIssue DESC) as BookIssueRank FROM Books )SELECT BookIssue FROM CTE WHERE BookIssueRank=1; Or you could let a sub-select get the max(bookissue) and use that in the where clause. SELECT BookIssue FROM Books WHERE BookIssue = (SELECT MAX(BookIssue) FROM books)
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@magnus-ahlkvist This solution might not work correctly if (in the above example) there happen to be any number other than four of the latest book. The question seems to require that four and only four issues be returned. We might be missing some information from the OP.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.