Re: SQL Puzzle - too many dimensions
Zapanaz wrote:
I know this is a java group, not a SQL group, I am trying to bounce
this off as many heads as I can and most Java programmers I know are
good at SQL.
I truly hope you aren't saying that you multi-posted your question to multiple
groups. Cross-posting would make sense, but multi-posting is a sin.
So I've got a table, song_versions, in an Oracle database. It has
Don't let the idiot who spouts anti-Oracle sentiment without any engineering
basis affect your thinking at all. Oracle, DB2, PostgreSQL, Derby, H2, SQL
Server and a few others are excellent DBMS products. (MySQL is not.)
columns like this
SONG_ID | SONG_VERSION_ID | RATING |
SONG_ID is a key into the table songs. A song could be something like
this, 'Money (That's What I Want)' by Berry Gordie
http://www.youtube.com/watch?v=z6xkT7FMyTc
And I have three versions of this song, one by Barrett Strong, one by
The Beatles, and one by The Rolling Stones.
I also have another song, 'Butterfly Caught' by Massive Attack. I only
have the one version of this song.
So my table looks like this
SONG_ID | SONG_VERSION_ID | RATING |
Money (That's What I Want) | Barrett Strong | 3 |
Money (That's What I Want) | Rolling Stones | 2 |
Money (That's What I Want) | Beatles | 1 |
Butterfly Caught | Massive Attack | 1 |
The actual values for the ID columns in the table are integers, this
is just for clarity.
The "actual" IDs are the ones you show. The physical *surrogate* IDs for
which you use integers are a hack. They are not the real IDs; they are used
only for the convenience of implementation and should not be exposed to
domain-relevant logic.
My job is to come up with one SQL query which will return the
highest-rated version of each song.
Others have answered this so I will not repeat their good advice. The key
point in the solutions presented was sub-SELECTs, or correlated subqueries.
Study up on those.
So for Money, the highest rated version is the Barrett Strong
version. For Butterfly Caught, there is only one version, so I want to
return that, the Massive Attack version. So I would want my result to
be the Barrett Strong version of Money and the Massive Attack version
fo Butterfuly Caught.
I can't think of a way to do it.
To get the MAX(Rating), I would have to GROUP BY song_id. So I can get
the numerical highest rating for each song, I can get a
song_id | rating
result set, but what I need is the song_version_id corresponding to
that rating.
Or I don't know, maybe something like
select song_version_id
from song_versions
where rating =
(select max(rating)
from song_versions
where song_id = [??? I don't know ... the same song ID I'm talking
about in the outer query]
)
If I group by song_id, I lose the song_version_id.
(Some DBs maybe I could cheat and return columns that aren't group by
columns, but oracle won't let me pull that one.
I just tried this in MySql
select song_id, song_version_id, max(rating)
from song_versions
group by song_id
and it worked, I got the Barrett Strong song_version_id, but then
there's nothing in my query that actually gaurantees that result, so I
don't know if I would want to depend on that in real code.
But anyway, this is in an Oracle DB, and they don't let you include
columns in a group-by query that aren't explicitly group-by that way.
)
Ummm, that's SQL, not just Oracle. Kinda hard to use an SQL-based product if
you don't know the language and what is standard for it.
And you're right, this is a Java newsgroup. People here do know some SQL, but
you should not be surprised if you get a Java coding solution here instead of
an SQL one, say one involving JPA. A SQL or Oracle group is much more
appropriate for this question.
It would help if you stick around and participate in future discussions and
don't just eff us then forget us. We love to help, as you saw from the high
quality of answers others have given you, but there is a quid pro quo.
--
Lew
Honi soit qui mal y pense.
http://upload.wikimedia.org/wikipedia/commons/c/cf/Friz.jpg