Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.java.programmer > #3770

SQL Puzzle - too many dimensions

Show all headers | View raw

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"

Back to comp.lang.java.programmer | Previous | Next — Next in thread | Find similar

Thread

SQL Puzzle - too many dimensions Zapanaz <http://joecosby.com/code/mail.pl@foo.com> - 2011-05-07 16:13 -0700
  Re: SQL Puzzle - too many dimensions Lawrence D'Oliveiro <ldo@geek-central.gen.new_zealand> - 2011-05-08 14:00 +1200
    Re: SQL Puzzle - too many dimensions Robert Klemme <shortcutter@googlemail.com> - 2011-05-08 13:08 +0200
      Re: SQL Puzzle - too many dimensions Lawrence D'Oliveiro <ldo@geek-central.gen.new_zealand> - 2011-05-09 00:01 +1200
        Re: SQL Puzzle - too many dimensions Lew <noone@lewscanon.com> - 2011-05-08 08:08 -0400
  Re: SQL Puzzle - too many dimensions Arved Sandstrom <asandstrom3minus1@eastlink.ca> - 2011-05-08 01:02 -0300
    Re: SQL Puzzle - too many dimensions Robert Klemme <shortcutter@googlemail.com> - 2011-05-08 13:03 +0200
      Re: SQL Puzzle - too many dimensions Arved Sandstrom <asandstrom3minus1@eastlink.ca> - 2011-05-08 11:14 -0300
        Re: SQL Puzzle - too many dimensions Robert Klemme <shortcutter@googlemail.com> - 2011-05-08 23:37 +0200
    Re: SQL Puzzle - too many dimensions Zapanaz <http://joecosby.com/code/mail.pl@foo.com> - 2011-05-08 16:06 -0700
      Re: SQL Puzzle - too many dimensions Robert Klemme <shortcutter@googlemail.com> - 2011-05-09 22:10 +0200
        Re: SQL Puzzle - too many dimensions Lawrence D'Oliveiro <ldo@geek-central.gen.new_zealand> - 2011-05-10 13:50 +1200
          Re: SQL Puzzle - too many dimensions Robert Klemme <shortcutter@googlemail.com> - 2011-05-10 00:06 -0700
            Re: SQL Puzzle - too many dimensions Lawrence D'Oliveiro <ldo@geek-central.gen.new_zealand> - 2011-05-13 22:55 +1200
              Re: SQL Puzzle - too many dimensions Robert Klemme <shortcutter@googlemail.com> - 2011-05-15 18:52 +0200
          Re: SQL Puzzle - too many dimensions Arved Sandstrom <asandstrom3minus1@eastlink.ca> - 2011-05-10 06:35 -0300
            Re: SQL Puzzle - too many dimensions Lawrence D'Oliveiro <ldo@geek-central.gen.new_zealand> - 2011-05-13 22:57 +1200
              Re: SQL Puzzle - too many dimensions Lew <noone@lewscanon.com> - 2011-05-13 07:45 -0400
              Re: SQL Puzzle - too many dimensions Arved Sandstrom <asandstrom3minus1@eastlink.ca> - 2011-05-15 13:09 -0300
                Re: SQL Puzzle - too many dimensions Robert Klemme <shortcutter@googlemail.com> - 2011-05-15 18:56 +0200
  Re: SQL Puzzle - too many dimensions Roedy Green <see_website@mindprod.com.invalid> - 2011-05-07 23:32 -0700
    Re: SQL Puzzle - too many dimensions Roedy Green <see_website@mindprod.com.invalid> - 2011-05-07 23:36 -0700
      Re: SQL Puzzle - too many dimensions Lawrence D'Oliveiro <ldo@geek-central.gen.new_zealand> - 2011-05-08 20:43 +1200
        Re: SQL Puzzle - too many dimensions Robert Klemme <shortcutter@googlemail.com> - 2011-05-09 01:27 -0700
  Re: SQL Puzzle - too many dimensions Lew <noone@lewscanon.com> - 2011-05-08 07:45 -0400

csiph-web