Query to get the highest rank of each item
Solution 1
To set up:
CREATE TABLE Book
(
id int PRIMARY KEY,
name text not null
);
CREATE TABLE Category
(
id int PRIMARY KEY,
name text not null
);
CREATE TABLE BookCategory
(
book_id int,
category_id int,
rank numeric not null,
primary key (book_id, category_id)
);
INSERT INTO Book VALUES
(1, 'On Writing'),
(2, 'Zen teachings'),
(3, 'Siddharta');
INSERT INTO Category VALUES
(2, 'Writing'),
(5, 'Spiritual'),
(9, 'Buddism');
INSERT INTO BookCategory VALUES
(1, 2, 34.32),
(1, 5, 24.23),
(1, 9, 54.65),
(2, 5, 27.33),
(2, 9, 28.32),
(3, 2, 30.43),
(3, 5, 27.87);
The solution:
SELECT Book.name
FROM (
SELECT DISTINCT ON (book_id)
*
FROM BookCategory
ORDER BY book_id, rank DESC
) t
JOIN Book ON Book.id = t.book_id
WHERE t.category_id = 2
ORDER BY t.rank;
Logically, the subquery in the FROM
clause generates a relation with the highest ranking category for each book, from which you then select the books in that category and order them by the ranking in that category.
Results:
name ----------- Siddharta (1 row)
Solution 2
add another column to calculate rank:
dense_rank() OVER (PARTITION BY book."name" ORDER BY bookcat."rank"
s ASC) AS rank
Solution 3
Is this what you want?
SELECT
book.name, mx.max_rank
FROM
(SELECT
max(rank) AS max_rank , book_id
FROM BookCategory WHERE category_id = 2
GROUP BY
book_id
) mx
JOIN Book ON
mx.book_id = Book.id
If I understand your question correctly, you need to get the maximum for a given category for every book in BookCategory (that is what the inner select does) and then simply join it to the Book table on book_id.
The whole example is on SQL Fiddle
EDIT:
I see that there is already an accepted answer, but for the sake of completeness, here is my answer following the clarification of the question:
SELECT
Book.name
FROM
(SELECT max(rank) AS max_rank, book_id AS bid
FROM BookCategory GROUP BY book_id
) mx
JOIN BookCategory ON
rank = max_rank
AND book_id = bid
JOIN Book
ON book_id = Book.id
WHERE category_id = 2
On SQL Fiddle.
Rod0n
Updated on August 01, 2022Comments
-
Rod0n almost 2 years
I've the(simplified) following model:
Book id name BookCategory book_id category_id rank Category id name
With a given category id, I'd like to get the books having that category as the highest ranked one.
I'll give an example to be more clear about it:
Book id name --- ------- 1 On Writing 2 Zen teachings 3 Siddharta BookCategory book_id category_id rank --- ------- ----- 1 2 34.32 1 5 24.23 1 9 54.65 2 5 27.33 2 9 28.32 3 2 30.43 3 5 27.87 Category id name --- ------- 2 Writing 5 Spiritual 9 Buddism
The result for category_id = 2 would be the book with id = 3.
This is the query I'm running:
SELECT book."name" AS bookname FROM bookcategory AS bookcat LEFT JOIN book ON bookcat."book_id" = book."id" LEFT JOIN category cat ON bookcat."category_id" = cat."id" WHERE cat."id" = 2 ORDER BY bookcat."rank"
This is not the right way to do it because it doesn't select the max rank of each book. I've yet to find a proper solution.
Note: I'm using the postgresql 9.1 version.
Edit:
DB Schema (taken from martin's SQL Fiddle answer):
create table Book ( id int, name varchar(16) ); insert into Book values(1, 'On Writing'); insert into Book values(2, 'Zen teachings'); insert into Book values(3, 'Siddharta'); create table BookCategory ( book_id int, category_id int, rank real ); insert into BookCategory values(1,2,34.32); insert into BookCategory values(1,5,24.23); insert into BookCategory values(1,9,54.65); insert into BookCategory values(2,5,27.33); insert into BookCategory values(2,9,28.32); insert into BookCategory values(3,2,30.43); insert into BookCategory values(3,5,27.87); create table Category ( id int, name varchar(16) ); insert into Category values(2, 'Writing'); insert into Category values(5,'Spiritual'); insert into Category values(9, 'Buddism');
-
kgrittn almost 12 yearsIf you could show the table structure and data for your example as
CREATE TABLE
andINSERT
statements, people could copy and paste that to a test database to try out solutions before posting.
-
-
Rod0n almost 12 yearsThank you for your answer martin, but the result is not the correct one. The query is returning books with id 1 and 3 but the right response is only the book with id = 3(siddartha). The max rank for the book with id = 1 is the one with category id = 9.