CODEWITHSUNDEEP
sqlite
Carsten
Carsten

Reputation: 2211

How to get the "top 10" numbers per object in SQLITE?

I have a SQLite-table with columns author, book, release_date and seller.

Now I need to find a way to get only the top 10 books of each author with the latest release_date.

If possible, I also need the seller, that appears "most often" in the top 10 latest released books per author. The result should be a simple table with author and seller only.

This problem is really driving me crazy. Is at minimum one part possible in a single SQLite-query???

Upvotes: 0

Views: 96

Answers (1)

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131774

SQLite v3.25 which came out in September 2018 added window functions.

You can calculate the rank of a book by date for each author with : 

CREATE TABLE books
(
  author varchar(10),
  title varchar(10),
  release date
);
INSERT INTO books VALUES 
('aaa','ta1','2018-01-01'),
('aaa','ta2','2018-02-01'),
('aaa','ta3','2018-03-01'),
('aaa','ta4','2018-05-01'),
('bbb','tb1','2018-05-01'),
('bbb','tb2','2018-06-01')
;

SELECT
    author,
    title,
    release, 
    row_number() OVER (partition by author ORDER BY release desc) AS row_number 
FROM books

The function row_number() OVER (partition by author ORDER BY release desc) AS row_number calculates the row number for each author if the rows are ordered by release date.

This produces :

author  title   release     row_number
aaa     ta4     2018-05-01  1
aaa     ta3     2018-03-01  2
aaa     ta2     2018-02-01  3
aaa     ta1     2018-01-01  4
bbb     tb2     2018-06-01  1
bbb     tb1     2018-05-01  2

Once you have the row number, you can filter the top N items with a simple WHERE row_number <= N, eg for the last 2 books per author :

select * from (
SELECT
    author,
    title,
    release, 
    row_number() OVER (partition by author ORDER BY release desc) AS row_number 
FROM books )
where row_number<=2

This returns :

author  title   release     row_number
aaa    ta4      2018-05-01  1
aaa    ta3      2018-03-01  2
bbb    tb2      2018-06-01  1
bbb    tb1      2018-05-01  2

Upvotes: 1

Related Questions