CODEWITHSUNDEEP
sqlrubypostgresqlsql-order-bysql-limit
charleszardo
charleszardo

Reputation: 189

Why does ORDER BY only work properly in my query when used with LIMIT?

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.

Upvotes: 0

Views: 113

Answers (1)

charleszardo
charleszardo

Reputation: 189

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.

Upvotes: 2

Related Questions