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

Question

question

Mhlengi asked Dec 7, '16

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

sql query database top

capture.png (12.1 KiB)

10 |1200

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

Answer 1 · 2016-12-07T13:29:01Z

Magnus Ahlkvist answered Dec 7, '16

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

10 |1200

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

GPO commented · Dec 11, 2016 at 05:02 AM

@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 ·

question