CODEWITHSUNDEEP
sqlsql-order-by
Dcopper
Dcopper

Reputation: 1

SQL ORDER BY NUMBER OF ENTRIES

I need to do the following data query in SQLite:

  1. order by the authors that have the max number of books in library
  2. In case of ties, order by the min number of days that you need to read all the books by a particular author
  3. Then, order by the min number of days that you need to read a particular book;
  4. Finally, order by the title of the books (book column).

So far this is what I got:

SELECT author, book from book_library
GROUP BY book
ORDER BY 
author DESC, 
COUNT(book), 
total_number_of_pages/speed ASC, 
(total_number_of_pages-pages_read)/speed ASC, 
book ASC;

It runs, but doesn't get me the desired outcome.

Output should only have two columns, author AND book

[Image Of The Data Structure]

1

Upvotes: 0

Views: 1104

Answers (3)

Joel Coehoorn
Joel Coehoorn

Reputation: 416101

It's not clear in the question how you want to handle pages_read, whether that applies to the total for the Author, for the Book, both, or neither. I opted to apply the pages_read only to the author, and not the book, so you have an example of both and can easily adapt to what you actually need.

The other thing to understand is you need to aggregate by Author (not Book!) to know how to do the ordering, but still want to show every book. This means you will need multiple passes through the data... ie a subquery of some kind. I opted to use a CTE (Common Table Expression).

WITH AuthorAggregate AS (
    SELECT Author,
        COUNT(book) As TotalBooks,
        SUM( ((total_number_of_pages - pages_read) * 1.0) / speed ) DaysToRead
    FROM book_library
    GROUP BY Author
)
SELECT bl.ID, bl.Author, bl.Book,
    bl.Pages_read, bl.Total_number_of_pages, bl.Speed
FROM book_libary bl
INNER JOIN AuthorAggregate aa ON aa.Author = bl.Author
ORDER BY aa.TotalBooks DESC, aa.DaysToRead, 
         (bl.total_number_of_pages * 1.0 / bl.speed), bl.book

One final step I took was ensuring the division operations are treated as floating point operations rather than integer operations.

Upvotes: 0

Vishal Murali
Vishal Murali

Reputation: 1

    WITH base AS(
SELECT id, 
        author,
        book, 
        pages_read, 
        total_number_of_pages, 
        speed, 
        (total_number_of_pages - pages_read)/speed AS days_left_to_finish_book
  FROM library),
  
  count_books AS
(SELECT author, count(id) as count_books
 FROM library
 GROUP BY author),
 
  total_days_by_author AS
  (SELECT author,
          SUM(days_left_to_finish_book) as total_days_by_author
   FROM base
   GROUP BY author)
   
   SELECT base.author, book, count_books, total_days_by_author, days_left_to_finish_book
   FROM base JOIN count_books 
      ON base.author = count_books.author
   JOIN total_days_by_author
      ON base.author = total_days_by_author.author
    ORDER BY count_books DESC, total_days_by_author, days_left_to_finish_book, book

Upvotes: 0

SBI
SBI

Reputation: 766

Try joining to a inner query that groups on authors like below:

SELECT 
      author, 
      book 
   from 
      book_library B
         JOIN ( SELECT 
                      author,
                      count(book) AS bookcount,
                      sum((total_number_of_pages - pages_read) 
                             / speed) as min_days_to_read_all_books 
                   from 
                      book_library
                   group by 
                      author) A 
            ON B.author = A.author
   ORDER BY 
      A.bookcount DES,
      A.min_days_to_read_all_books,
      (B.total_number_of_pages - B.pages_read) / B.speed,
      B.book

The inner query orders the authors by total number of books, days it will take to finish reading all his books(remaining pages, you can remove the subtraction if want to read all the pages). We have a record for each author coming out of this inner query. Now to get a record for each book, only thing left to do is to join again to the table with author as the key, and order by number of days it will take to read remaining pages in the book and then the title.

The order by clauses can be arranged in the order needed.

Note - I updated the query to get the number of days it will take to read each book, somehow missed it before.

Upvotes: 0

Related Questions