Mysql find the book with the highest rating for each country. If there is a tie "print" th-CodePudding

I have the following tables:

CREATE TABLE `country` (
  `name` VARCHAR(60) NOT NULL,
  `code` VARCHAR(3) UNIQUE NOT NULL,
  PRIMARY KEY  (`code`)
);

CREATE TABLE `user` (
  `userId` INT UNIQUE NOT NULL AUTO_INCREMENT,
  `country` VARCHAR(3) NOT NULL,
  `age` INT NOT NULL,
  PRIMARY KEY  (`userId`),
  CONSTRAINT `fk_user_country` FOREIGN KEY (`country`) REFERENCES `country`(`code`)
);

CREATE TABLE `bookRating` (
  `userId` INT NOT NULL,
  `isbn` VARCHAR(13) NOT NULL,
  `rate` INT NOT NULL,
  `date` DATE NOT NULL, 
  CONSTRAINT `fk_bookRating_user` FOREIGN KEY (`userId`) REFERENCES `user`(`userId`),
  CONSTRAINT `fk_bookRating_book` FOREIGN KEY (`isbn`) REFERENCES `book`(`isbn`)
);

CREATE TABLE `book` (
  `isbn` varchar(13) UNIQUE NOT NULL,
  `bookTitle` VARCHAR(280),
  `bookAuthor` VARCHAR(150),
  `yearPublication` int(4),
  -- `yearPublication` must be an integer because we have value less that 1901 in dataset
  `publisher` VARCHAR(135),
  PRIMARY KEY  (`isbn`),
  CONSTRAINT `publication_yea_chk` check ((`yearPublication` > -1) && (`yearPublication` < 2101))
);

As I am saying on the title I want to find the book with the highest average rating, For each country

I have tried this query:

select T1.name, T1.BookTitle, Rate
from    
(
    select C.Code, AVG(BR.rate) MAXRating
    from   `bookRating` BR
            inner join `book` B on BR.isbn = B.isbn
            INNER JOIN `USER` U ON BR.UserID = U.USERId
            INNER JOIN `COUNTRY` C ON U.country = C.Code
    group by C.Code
) T
inner join 
(
    select C.Code, C.name, B.BookTitle, BR.ISBN, BR.rate
    from   `bookRating` BR
            inner join `book` B on BR.isbn = B.isbn
            INNER JOIN `USER` U ON BR.UserID = U.USERId
            INNER JOIN `COUNTRY` C ON U.country = C.Code
) T1 ON T.Code = T1.Code AND T.MAXRATING = T1.RATE;

I am pretty sure this works. But I want to make it like If 2 or more books have the same average rating I want the one with the highest number of ratings.

I figured that I could use an If() statement, but how could I If(... , a condition)

How could I do it?

UPDATE

I have made the database and inserted some info in db fidle: https://www.db-fiddle.com/f/s6wKhKhxXMX1W2x9VZn9da/1

CodePudding user response：

You can join the tables, aggregate by country and book to get all average ratings and use window functions MAX() and FIRST_VALUE() on the results of the aggregation to get the book with the highest average for each country:

SELECT DISTINCT c.name, 
       FIRST_VALUE(b.bookTitle) OVER (
         PARTITION BY c.Code
         ORDER BY AVG(r.rate) DESC, COUNT(*) DESC
       ) bookTitle,
       MAX(AVG(r.rate)) OVER () AverageRating
FROM country c
INNER JOIN users u ON u.country = c.Code
INNER JOIN bookRating r ON r.UserID = u.UserID
INNER JOIN book b ON b.isbn = r.isbn
GROUP BY c.Code, b.isbn;

See the demo.