CODEWITHSUNDEEP
mysqlsql
Nick
Nick

Reputation: 21

SQL - finding out the 5 highest numbers in a table

I have some tables that all contain information about books, such as titles, pages, price, royalty, etc. I would like to use these tables to find the 5 authors that have the highest royalty rates.

Here are my tables:

CREATE TABLE authors
  (
  au_id    CHAR(3)     NOT NULL,
  au_fname VARCHAR(15) NOT NULL,
  au_lname VARCHAR(15) NOT NULL,
  phone    VARCHAR(12)         ,
  address  VARCHAR(20)         ,
  city     VARCHAR(15)         ,
  state    CHAR(2)             ,
  zip      CHAR(5)             ,
  CONSTRAINT pk_authors PRIMARY KEY (au_id)
  )ENGINE = InnoDB;


CREATE TABLE royalties
  (
  title_id     CHAR(3)      NOT NULL,
  advance      DECIMAL(9,2)         ,
  royalty_rate DECIMAL(5,2)         ,
  CONSTRAINT pk_royalties PRIMARY KEY (title_id)
  )ENGINE = InnoDB;

So far, I can only select the highest royalty rates. I tried using the TOP (5) function, but that did not work for me. My code is below.

SELECT au_fname, au_lname
FROM authors
WHERE au_id IN
(SELECT au_id
FROM title_authors
WHERE title_id IN
(SELECT title_id
FROM royalties
WHERE royalty_rate = (SELECT MAX(royalty_rate) FROM royalties)))

How can I go about finding the 5 authors that have the highest royalty rates?

Upvotes: 0

Views: 212

Answers (2)

spencer7593
spencer7593

Reputation: 108410

MySQL does not support the TOP keyword. Some databases other than MySQL (e.g. SQL Server, Teradata, et al.) do support that syntax.

MySQL provides similar functionality with the LIMIT keyword.

 SELECT t.foo
   FROM mytable t
 ORDER BY t.bar DESC
 LIMIT 5

As far as getting the result, I strongly recommend you use join operations:

  SELECT a.au_fname
       , a.au_lname
    FROM authors a
    JOIN title_authors t 
      ON t.au_id = a.au_id
    JOIN royalties r
      ON r.title_id = t.title_id
   ORDER BY r.royalty_rate DESC
   LIMIT 5

Note that if an author has two (or more) titles with the highest royalty_rate, that author will be returned multiple times.

If you want five different authors, find the maximum royalty rate for each author, and order by that.

  SELECT a.au_fname
       , a.au_lname
    FROM authors a
    JOIN title_authors t 
      ON t.au_id = a.au_id
    JOIN royalties r
      ON r.title_id = t.title_id
   GROUP BY a.au_id
   ORDER BY MAX(r.royalty_rate) DESC
   LIMIT 5

If more than five authors have the same highest royalty_rate, then which five of those authors is indeterminate. You could add additional expressions to the ORDER BY clause to make the result more deterministic.

   ORDER BY MAX(r.royalty_rate) DESC, a.id DESC

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269913

You should learn to use joins and aggregation:

SELECT a.au_fname, a.au_lname
FROM authors a JOIN
     title_authors ta
     USING (au_id) JOIN    
     royalties r
     USING (title_id)
ORDER BY royalty_rate DESC
LIMIT 5;

MySQL uses LIMIT to fetch the first five rows.

Upvotes: 0

Related Questions