Path: csiph.com!x330-a1.tempe.blueboxinc.net!aioe.org!news.glorb.com!postnews.google.com!news2.google.com!Xl.tags.giganews.com!border1.nntp.dca.giganews.com!nntp.giganews.com!local2.nntp.dca.giganews.com!news.giganews.com.POSTED!not-for-mail NNTP-Posting-Date: Sat, 07 May 2011 18:13:19 -0500 From: Zapanaz Newsgroups: comp.lang.java.programmer Subject: SQL Puzzle - too many dimensions Date: Sat, 07 May 2011 16:13:52 -0700 Message-ID: X-Newsreader: Forte Agent 6.00/32.1186 MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit Lines: 90 X-Usenet-Provider: http://www.giganews.com X-Trace: sv3-Kz3clDq5DxpRjXX16d0mck7qzuOb5c+INogfyl4GuUMTHvGYjl9vE5smupSN/yd2XXXhn6PsydG9IHq!4k/vArlgNV+d/PqpWWEZ1vhaj/ovS84Q/Abrg2Vd1tOSLLf12H6UKUoSIkrUeRXMc4q4X2o= X-Complaints-To: abuse@giganews.com X-DMCA-Notifications: http://www.giganews.com/info/dmca.html X-Abuse-and-DMCA-Info: Please be sure to forward a copy of ALL headers X-Abuse-and-DMCA-Info: Otherwise we will be unable to process your complaint properly X-Postfilter: 1.3.40 X-Original-Bytes: 3943 Xref: x330-a1.tempe.blueboxinc.net comp.lang.java.programmer:3770 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. So I've got a table, song_versions, in an Oracle database. It has 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. My job is to come up with one SQL query which will return the highest-rated version of each song. 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. ) -- Zapanaz International Satanic Conspiracy Customer Support Specialist http://joecosby.com/ You know you're in Saskatchewan when you can sit on your front porch and watch your dog run away for three days :: Currently listening to Todesfuge, 2003, by Diamanda Galás, from "Defixiones, Will and Testament"