Reputation: 2211
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
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