Path: csiph.com!x330-a1.tempe.blueboxinc.net!aioe.org!news.swapon.de!fu-berlin.de!uni-berlin.de!individual.net!not-for-mail
From: Robert Klemme <shortcutter@googlemail.com>
Newsgroups: comp.lang.java.programmer
Subject: Re: SQL Puzzle - too many dimensions
Date: Sun, 08 May 2011 13:08:59 +0200
Lines: 29
Message-ID: <92nbumFrggU1@mid.individual.net>
References: <tnibs6hnfdp2cav0u74d8heeqsdkvg1c6a@4ax.com> <iq4tf9$ccn$1@lust.ihug.co.nz>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Trace: individual.net idtgV1toK44V/D77yeVnIwBRoPGGxsL8LJqyacdGsbgnC/iwQ=
Cancel-Lock: sha1:QchIpCCXMUZalEX9Vv1nIZmfCeM=
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US; rv:1.9.2.17) Gecko/20110414 Lightning/1.0b2 Thunderbird/3.1.10
In-Reply-To: <iq4tf9$ccn$1@lust.ihug.co.nz>
X-Antivirus: avast! (VPS 110507-1, 07.05.2011), Outbound message
X-Antivirus-Status: Clean
Xref: x330-a1.tempe.blueboxinc.net comp.lang.java.programmer:3802

On 08.05.2011 04:00, Lawrence D'Oliveiro wrote:
> In message<tnibs6hnfdp2cav0u74d8heeqsdkvg1c6a@4ax.com>,  wrote:
>
>> 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.
>
> Sounds like as good a time as any to move away from Oracle.

That's ridiculous.  This query can be easily done with Oracle.  I don't 
have an Oracle handy but this should work:

select sv.song_version_id
  from song_versions sv
  where sv.rating =
   (select max(rating)
     from song_versions sm
     where sm.song_id = sv.song_id
   )

Apart from that there are the approaches presented by Arved (join and 
analytic SQL).

Kind regards

	robert

-- 
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/