0
Album
Title                                     Artist                Year Type        Rating
My World                                  Justin Bieber         2009 STUDIO           4
My Worlds: The Collection                 Justin Bieber         2010 COMPILATION      4
21                                        Adele                 2011 STUDIO           5
Adele Live at the Royal Albert Hall       Adele                 2011 LIVE             4
Get to Heaven                             Everything Everything 2015 STUDIO           5
One of the boys                           Katy Perry            2008 STUDIO           3
Overexposed                               Maroon 5              2012 STUDIO           5
Live From Le Cabaret: In Montreal. Quebec Maroon 5              2008 LIVE             4
Pure Heroine                              Lorde                 2013 STUDIO           4

So above is the Album, how can I get each artist's own highest rating album?

Thanks in advance.

pah
  • 4,700
  • 6
  • 28
  • 37
ZHICHU
  • 11
  • 2
  • It is a good practice to inline the image in your question instead of providing a link that opens in another tab. This makes your question easier to read and understand. – dant3 Nov 03 '15 at 23:53
  • It's better practice to dispense with a linked image altogether – Strawberry Nov 04 '15 at 00:07

2 Answers2

2

Here's one option joining to a subquery using the max aggregate:

select a.title, a.artist
from album a join (
    select artist, max(rating) maxrating
    from almum
    group by artist) t on a.artist = t.artist and a.rating = t.maxrating

This can return multiple titles per artist if they share the highest rating.

And perhaps even easier with an outer join:

select a.*
from album a                   
  left join album a2            
      on a.artist = a2.artist and a.rating < a2.rating
where a2.rating is null
sgeddes
  • 62,311
  • 6
  • 61
  • 83
  • I like the second answer. It's confusing at first but very elegant... and no subqueries needed! – soulfreshner Nov 04 '15 at 00:03
  • 1
    @soulfreshner The first solution is orders of magnitude faster than the second. – Strawberry Nov 04 '15 at 00:08
  • @Strawberry -- I thought they were pretty comparable in `mysql`. I generally use the first option (really because of readability) -- does it perform better as well? – sgeddes Nov 04 '15 at 00:15
  • @sgeddes No. Not remotely comparable. I mean, your first solution is likely to be as good as or better than any other method. – Strawberry Nov 04 '15 at 00:17
  • I'm old school, so I like the second method too (I've even seen it called the 'Strawberry' method from time to time - but while it's nice to have something named after me, I'd prefer it to be the sgeddes method ;-) ) – Strawberry Nov 04 '15 at 00:21
  • @Strawberry Maybe, I'm not sure. On the one hand, the latter removes an aggregate subquery but on the other it joins multiple times on the 'rating' field. I would think that for small numbers of items per artist, the second would be faster, but I would love to see some actual benchmarks. Either way, the second solution just seems smart and out of the box, which is why I like it :) – soulfreshner Nov 04 '15 at 00:23
  • "Maybe, I'm not sure" @soulfreshner It's OK. I am. Sparsity of results has negligible impact on the relative performance of these queries, – Strawberry Nov 04 '15 at 00:27
  • @Strawberry we live we learn :). Could you supply a link to more info on this? I'm genuinely interested. – soulfreshner Nov 04 '15 at 00:32
  • Check out EXPLAINEXTENDED's blog posts on the subjects. While the tests might be slightly out-of-date, I doubt the relative performance will have moved much. I think Roland Bouman and/or Guiseppe Maxia may have explored something similar once upon a time too. But you could knock up a test yourself in 10 minutes. – Strawberry Nov 04 '15 at 00:34
-1
SELECT* 
FROM(SELECT* FROM`Album` ORDER BY `Artist`, `Rating` DESC) x
GROUP BY `Artist`

See this question for more info, it's quite similar. Basically, sort it so that you have the table rearranged by artist, with the highest rating at the top for each, then group it to pull out the first record for each.

Community
  • 1
  • 1
Logan Bentley
  • 284
  • 1
  • 7
  • I never liked this as a solution -- just didn't feel right imo... I thought it actually could return arbitrary values, but I could be mistaken. – sgeddes Nov 03 '15 at 23:59
  • 1
    The return values for this is not guaranteed... you are grouping by artist, there is no guarantee that you will get the first record. Though this does seem to be the case with mysql. – soulfreshner Nov 04 '15 at 00:01