0

I'm going through some SQL practice problems. The one I'm currently baffled by asks to select the artist who has recorded the most rock albums, as well as the number of albums. While putting to the query together piece-by-piece I ran into an issue ordering by number of rock albums.

  SELECT
    a.artist, COUNT(DISTINCT a.asin)
  FROM
    albums a
  JOIN
    styles s ON a.asin = s.album
  WHERE
    s.style LIKE '%Rock%'
  GROUP BY
    a.artist
  ORDER BY
    COUNT(DISTINCT a.asin) DESC

Yields something like:

[["Alanis Morissette", "1"], ["Anne Murray", "1"], ["Billy Joel", "2"] ...      
["The Beach Boys", "1"], ["The Beatles", "7"], ["The Clash", "1"] ...
["Trash Can Sinatras", "1"], ["Travis", "1"], ["U2", "2"], ["Van Morrison", "1"]]

The results are still ordered by artist rather than count. However, when I add

LIMIT 1

to the query, ORDER BY seems to work properly as the correct answer (["The Beatles", "7"]) moves from the middle to the top as the only answer that's yielded.

Could someone please explain what's going on here?

NOTE: I believe it might be helpful to include that the code is written in Ruby and run through the following function:

require 'pg'

def execute(sql)
  conn = PG::Connection.open(:dbname => 'sqlzoo')
  query_result = conn.exec(sql).values
  conn.close
  query_result
end

Also, this is the test (RSpec) being used for the query:

describe "rock_superstars" do
  it "selects the name of the most prolific rock artist" do
    expect(rock_superstars).to contain_exactly(
      ["The Beatles", "7"]
    )
  end
end

The results above are the output from this test, hence the form of an array.

charleszardo
  • 179
  • 12
  • 1
    If everything is as you said, then you should get your expected results. Just for fun, what happens if you give an alias to the count, and order by that alias?: `SELECT a.artist, COUNT(DISTINCT a.asin) as N FROM ........... ORDER BY N DESC`? – Lamak Aug 03 '16 at 15:52
  • I removed the incompatible database tags. Feel free to add the tag for the database you are really using. – Gordon Linoff Aug 03 '16 at 15:52
  • @Lamak even with the alias, the results are still ordered by artist. Seems completely bizarre to me. – charleszardo Aug 03 '16 at 16:04
  • Based on your output, it looks like your doing more than your code is showing - `ARRAY_AGG`, etc.? If so, can you show that - it might impact the final ordering – Nick Aug 03 '16 at 16:12
  • @Nicarus, I did fail to mention that I'm writing this in ruby within a provided skeleton and running RSpec tests that came with the set of problems. I've added a function that processes the queries to my post above. – charleszardo Aug 03 '16 at 16:23
  • Can you run your exact query (what you posted) directly in psql or a PostgreSQL client - outside of Ruby? I imagine the ordering is correct there. I would bet that you lose that ordering in Ruby. – Nick Aug 03 '16 at 16:25
  • @Nicarus - running directly through PostgreSQL did the trick. Thanks for your input. It never dawned on me that the problem could've been related to the queries being run through Ruby. – charleszardo Aug 03 '16 at 17:33
  • Good. If you were able to identify why the ordering was not preserved in Ruby and how to correct it, you should post that as answer to this and accept it. That way the next person searching for this or a similar issue will have this as a resource. – Nick Aug 03 '16 at 17:35
  • Certainly. I'll give it a try and see if I can identify the Ruby cause. – charleszardo Aug 03 '16 at 17:37

1 Answers1

2

This is an ORM issue, not a SQL issue. The perceived problem is due to the way RSpec is printing the failure message in this case.

Running the query through PostgreSQL worked perfectly fine and as expected.

The correct order is also seen when printing out the results in the test block right before

expect(rock_superstars).to ...

Therefore the issue seems to have to do with RSpec's contain_exactly. Upon failure it prints out actual collection contained: with a different sorting order than that which the query generates.

charleszardo
  • 179
  • 12