CODEWITHSUNDEEP
mysqlsorting
dev-null
dev-null

Reputation: 551

MySQL ordering by distinct count

I have a select query which produces the following:

select customers.city , books.title                                                                   
from loaned, books, customers                                                                         
where loaned.userID = customers.userID                                                                
and loaned.bookID = books.bookID 
+------------+-------------------------------+
| city       | title                         |
+------------+-------------------------------+
| Harrogate  | The cross rabbit              |
| Harrogate  | PHP and MySQL web development |
| Harrogate  | PHP and MySQL web development |
| Whitehaven | Greek Mythology               |
| Whitehaven | Dino-soaring                  |
| Whitehaven | Dino-soaring                  |
| Sale       | Magic tricks                  |
| Sale       | Magic tricks                  |
| Sale       | Magic tricks                  |
| Sale       | Dino-soaring                  |
| Sale       | Dino-soaring                  |
+------------+-------------------------------+
11 rows in set (0.00 sec)

I want to find the most popular title for each city and so I did the following:

group by city
order by count(distinct title) desc

however this doesn't produce the correct results. I get:

+------------+-------------------------------+
| city       | title                         |
+------------+-------------------------------+
| Sale       | Dino-soaring                  |
| Whitehaven | Dino-soaring                  |
| Harrogate  | PHP and MySQL web development |
+------------+-------------------------------+
3 rows in set (0.00 sec)

This appears to be sorted alphabetically, not by popularity. Having got the data I thought it would be easy to order it to how I require but it's not turned out that way. Do I need to do some sort of join or something more complicated than that?

Thanks in advance.

Upvotes: 4

Views: 765

Answers (4)

dev-null
dev-null

Reputation: 551

Thanks to everyone who answered. As this was a question for a test I didn't want to 'cut and paste' someone else's work but used their logic to make my own query. Here's what I got:

select city, title
from (
    select customers.city as city, books.title as title, count(books.title) as cnt
    from books, customers, loaned
    where loaned.userID = customers.userID
    and loaned.bookID = books.bookID
    group by title, city
    order by cnt desc) as tbl
group by city

Results:

+------------+-------------------------------+
| city       | title                         |
+------------+-------------------------------+
| Harrogate  | PHP and MySQL web development |
| Sale       | Magic tricks                  |
| Whitehaven | Dino-soaring                  |
+------------+-------------------------------+
3 rows in set (0.00 sec)

Upvotes: 1

ace
ace

Reputation: 7583

I'd approach this problem in 3 steps. First get the count of each book from each city.

select customers.city, books.title, count(books.title) as count
from loaned, books, customers
where loaned.userID = customers.userID
and loaned.bookID = books.bookID
group by customers.city, books.title

This query will return the following rows.

+------------+-------------------------------+-------+
| city       | title                         | count |
+------------+-------------------------------+-------+
| Harrogate  | The cross rabbit              | 1     |
| Harrogate  | PHP and MySQL web development | 2     |
| Whitehaven | Greek Mythology               | 1     |
| Whitehaven | Dino-soaring                  | 2     |
| Sale       | Magic tricks                  | 3     |
| Sale       | Dino-soaring                  | 2     |
+------------+-------------------------------+-------+

Using that data, then I'll use that to do a group by on each city with the most count.

select city, max(count) as count
from 
(
  select customers.city , books.title, count(books.title) as count
  from loaned, books, customers
  where loaned.userID = customers.userID
  and loaned.bookID = books.bookID
  group by customers.city, books.title
) as city_book_max_count
group by city

Which will return these rows,

+------------+-------+
| city       | count |
+------------+-------+
| Harrogate  | 2     |
| Whitehaven | 2     |
| Sale       | 3     |
+------------+-------+

Using the data from the 2 tables, we can then join them on city and the count, to get the corresponding books that match on both tables.

select city_book_count.city, city_book_count.title
from 
(
  select customers.city , books.title, count(books.title) as count
  from loaned, books, customers
  where loaned.userID = customers.userID
  and loaned.bookID = books.bookID
  group by customers.city, books.title
) as city_book_count
join
(
  select city, max(count) as count
  from 
  (
    select customers.city , books.title, count(books.title) as count
    from loaned, books, customers
    where loaned.userID = customers.userID
    and loaned.bookID = books.bookID
    group by customers.city, books.title
  ) as city_book_count_temp
  group by city
) as city_book_max_count
on city_book_count.city = city_book_max_count.city
  and city_book_count.count = city_book_max_count.count

Upvotes: 1

Vinit Prajapati
Vinit Prajapati

Reputation: 1613

I am removing customer table, as there is no output from this table

select customers.city , books.title , count(books.title) Total                       
from loaned, books, customers                                                                         
where loaned.userID = customers.userID                                                       
and loaned.bookID = books.bookID 
    group by customers.city , books.title order by 3 desc

Upvotes: 0

krishnang
krishnang

Reputation: 698

Try replacing the distinct title with just title and this should solve your issue..

Upvotes: 2

Related Questions