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

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

SQL Puzzle - too many dimensions

Back to article view | Back to comp.lang.java.programmer

Contents

  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

Page 1 of 2  [1] 2  Next page →

#3770 — SQL Puzzle - too many dimensions

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"

[toc] | [next] | [standalone]

#3775

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.

[toc] | [prev] | [next] | [standalone]

#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/

[toc] | [prev] | [next] | [standalone]

#3807

In message <92nbumFrggU1@mid.individual.net>, Robert Klemme wrote:

> 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.

That wasn’t quite what I meant.

[toc] | [prev] | [next] | [standalone]

#3810

On 05/08/2011 08:01 AM, Lawrence D'Oliveiro wrote:
> In message<92nbumFrggU1@mid.individual.net>, Robert Klemme wrote:
>
>> 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.
>
> That wasn’t quite what I meant.

Perhaps if you said what you actually meant, or even made sense, instead of 
dropping these trollish fragments to maintain plausible deniability and the 
ability to argue with whatever answer you get, that would be less insufferably 
rude than what you are doing.

-- 
Lew
Honi soit qui mal y pense.
http://upload.wikimedia.org/wikipedia/commons/c/cf/Friz.jpg

[toc] | [prev] | [next] | [standalone]

#3780

On 11-05-07 08:13 PM, 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.
> 
> 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.
> )
> 
You can try something like

select s.song_key, s.rating
from (
   select song_key, max(rating) max_rating
   from songs group by song_key
) mx inner join songs s
on s.song_key = mx.song_key and s.rating = mx.max_rating;

For that example I presupposed a table with columns like

SONG_ID   SONG_KEY   RATING

where the SONG_ID (for my own sanity) is simply a primary Key "id",
SONG_KEY is like your "SONG_ID", and RATING is "RATING".

On Oracle you could also do something like

select distinct song_key, max(rating) over (partition by song_key)
max_rating from songs;

which makes use of Oracle analytic functions.

AHS

[toc] | [prev] | [next] | [standalone]

#3801

On 08.05.2011 06:02, Arved Sandstrom wrote:
> On 11-05-07 08:13 PM, 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.
>>
>> 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.
>> )
>>
> You can try something like
>
> select s.song_key, s.rating
> from (
>     select song_key, max(rating) max_rating
>     from songs group by song_key
> ) mx inner join songs s
> on s.song_key = mx.song_key and s.rating = mx.max_rating;

Just an additional note: This is only necessary if you want to pull more 
columns from s.  If query is only interested in s.song_key, s.rating you 
only need the inline view's query.

> For that example I presupposed a table with columns like
>
> SONG_ID   SONG_KEY   RATING
>
> where the SONG_ID (for my own sanity) is simply a primary Key "id",
> SONG_KEY is like your "SONG_ID", and RATING is "RATING".
>
> On Oracle you could also do something like
>
> select distinct song_key, max(rating) over (partition by song_key)
> max_rating from songs;
>
> which makes use of Oracle analytic functions.

This should also work on PostgreSQL.

Kind regards

	robert

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

[toc] | [prev] | [next] | [standalone]

#3812

On 11-05-08 08:03 AM, Robert Klemme wrote:
> On 08.05.2011 06:02, Arved Sandstrom wrote:
>> On 11-05-07 08:13 PM, 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.
>>>
>>> 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.
>>> )
>>>
>> You can try something like
>>
>> select s.song_key, s.rating
>> from (
>>     select song_key, max(rating) max_rating
>>     from songs group by song_key
>> ) mx inner join songs s
>> on s.song_key = mx.song_key and s.rating = mx.max_rating;
> 
> Just an additional note: This is only necessary if you want to pull more
> columns from s.  If query is only interested in s.song_key, s.rating you
> only need the inline view's query.

That's correct - thanks for the elaboration. The full SQL I provided
above is as you said, more general purpose.

>> For that example I presupposed a table with columns like
>>
>> SONG_ID   SONG_KEY   RATING
>>
>> where the SONG_ID (for my own sanity) is simply a primary Key "id",
>> SONG_KEY is like your "SONG_ID", and RATING is "RATING".
>>
>> On Oracle you could also do something like
>>
>> select distinct song_key, max(rating) over (partition by song_key)
>> max_rating from songs;
>>
>> which makes use of Oracle analytic functions.
> 
> This should also work on PostgreSQL.
> 
> Kind regards
> 
>     robert
> 
Good to know. I'm used to the Oracle ones, but I'm certainly no DBA in
general. Now that you mentioned this, I researched things some. It looks
like not only Oracle but also DB2 have had a rich set of analytic
functions for quite a long time (I think DB2 calls them OLAP functions),
SQL Server has been incorporating them since SQL Server 2005 (SQL Server
2008 presumably better; I think SQL Server calls these ranking
functions), and PostgreSQL has them now also (generally speaking ANSI
SQL calls these things window functions; PostgreSQL 8.4 introduced
support and PostgreSQL 9.x is presumably better at it).

I use PostgreSQL regularly on my MacBook, but I've been stuck on 8.3 for
a few years.

MySQL I know very little about. The few articles I located on MySQL and
analytic functions gave me the impression that the support is poor or
missing, but perhaps I am wrong.

AHS

[toc] | [prev] | [next] | [standalone]

#3828

On 08.05.2011 16:14, Arved Sandstrom wrote:
> On 11-05-08 08:03 AM, Robert Klemme wrote:
>> On 08.05.2011 06:02, Arved Sandstrom wrote:
>>> On 11-05-07 08:13 PM, 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.
>>>>
>>>> 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.
>>>> )
>>>>
>>> You can try something like
>>>
>>> select s.song_key, s.rating
>>> from (
>>>      select song_key, max(rating) max_rating
>>>      from songs group by song_key
>>> ) mx inner join songs s
>>> on s.song_key = mx.song_key and s.rating = mx.max_rating;
>>
>> Just an additional note: This is only necessary if you want to pull more
>> columns from s.  If query is only interested in s.song_key, s.rating you
>> only need the inline view's query.
>
> That's correct - thanks for the elaboration. The full SQL I provided
> above is as you said, more general purpose.

Actually I (or we) overlooked that OP wanted the song_version_id - that 
would be one additional column.  So that then would be

select s.song_version_id, s.rating
from (
      select song_id, max(rating) max_rating
      from songs
      group by song_id
) mx
inner join songs s
on s.song_id = mx.song_id
and s.rating = mx.max_rating;

>>> For that example I presupposed a table with columns like
>>>
>>> SONG_ID   SONG_KEY   RATING
>>>
>>> where the SONG_ID (for my own sanity) is simply a primary Key "id",
>>> SONG_KEY is like your "SONG_ID", and RATING is "RATING".
>>>
>>> On Oracle you could also do something like
>>>
>>> select distinct song_key, max(rating) over (partition by song_key)
>>> max_rating from songs;
>>>
>>> which makes use of Oracle analytic functions.
>>
>> This should also work on PostgreSQL.

> Good to know. I'm used to the Oracle ones, but I'm certainly no DBA in
> general. Now that you mentioned this, I researched things some. It looks
> like not only Oracle but also DB2 have had a rich set of analytic
> functions for quite a long time (I think DB2 calls them OLAP functions),
> SQL Server has been incorporating them since SQL Server 2005 (SQL Server
> 2008 presumably better; I think SQL Server calls these ranking
> functions), and PostgreSQL has them now also (generally speaking ANSI
> SQL calls these things window functions; PostgreSQL 8.4 introduced
> support and PostgreSQL 9.x is presumably better at it).

Actually SQL Server's SQL engine isn't too bad.  With CTE (common table 
expression, Oracle has it, too, but calls it "subquery factoring clause" 
- and it has slightly different functionality IIRC [1]) you could do 
recursive queries (since 2005 IIRC) that in Oracle you needed non 
standard CONNECT BY syntax for.

> I use PostgreSQL regularly on my MacBook, but I've been stuck on 8.3 for
> a few years.
>
> MySQL I know very little about. The few articles I located on MySQL and
> analytic functions gave me the impression that the support is poor or
> missing, but perhaps I am wrong.

Same here but I wonder what the optimizer can do with arbitrary storage 
engines.  But this is a completely different topic...

Cheers

	robert


[1] 
http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/statements_10002.htm#i2161315

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

[toc] | [prev] | [next] | [standalone]

#3830

On Sun, 08 May 2011 01:02:00 -0300, Arved Sandstrom
<asandstrom3minus1@eastlink.ca> wrote:

>select 

>from (
>   select song_key, max(rating) max_rating
>   from songs group by song_key
>) mx

ah I didn't think of that. I don't think I've ever used a subquery as
a table like that before. Thanks very much.
-- 
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"

[toc] | [prev] | [next] | [standalone]

#3874

On 09.05.2011 01:06, Zapanaz wrote:
> On Sun, 08 May 2011 01:02:00 -0300, Arved Sandstrom
> <asandstrom3minus1@eastlink.ca>  wrote:
>
>> select
>
>>from (
>>    select song_key, max(rating) max_rating
>>    from songs group by song_key
>> ) mx
>
> ah I didn't think of that. I don't think I've ever used a subquery as
> a table like that before. Thanks very much.

That's called an "inline view".

Cheers

	robert

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

[toc] | [prev] | [next] | [standalone]

#3889

In message <92r02mF24dU1@mid.individual.net>, Robert Klemme wrote:

> That's called an "inline view".

The only reason there’s a special term for it is because SQL doesn’t treat 
tables/views as first-class objects.

This is an example of a situation where a little bit more orthogonality 
could have made the language much simpler.

[toc] | [prev] | [next] | [standalone]

#3902

On 10 Mai, 03:50, Lawrence D'Oliveiro <l...@geek-
central.gen.new_zealand> wrote:
> In message <92r02mF24...@mid.individual.net>, Robert Klemme wrote:
>
> > That's called an "inline view".
>
> The only reason there’s a special term for it is because SQL doesn’t treat
> tables/views as first-class objects.

There are no objects in SQL.  SQL is neither procedural nor object
oriented.  It is a declarative language implementing relational
algebra.

> This is an example of a situation where a little bit more orthogonality
> could have made the language much simpler.

This is an empty claim as long as you do not prove in what ways it
could be made simpler.  Lawrence, is there /anything/ you are
comfortable with the way it is?

Good luck

robert

[toc] | [prev] | [next] | [standalone]

#4046

In message <dddba9e2-7e37-4fa4-
bcb2-2e575deda114@w21g2000yqm.googlegroups.com>, Robert Klemme wrote:

> On 10 Mai, 03:50, Lawrence D'Oliveiro <l...@geek-
> central.gen.new_zealand> wrote:
>
>> In message <92r02mF24...@mid.individual.net>, Robert Klemme wrote:
>>
>> > That's called an "inline view".
>>
>> The only reason there’s a special term for it is because SQL doesn’t
>> treat tables/views as first-class objects.
> 
> There are no objects in SQL.  SQL is neither procedural nor object
> oriented.

I didn’t say it was “object-oriented”. In Computer Science, we use the term 
“first-class object” to refer to what you can do with objects, not object-
orientation <http://en.wikipedia.org/wiki/First_class_object>.

> It is a declarative language implementing relational
> algebra.

If it DID implement proper relational algebra as it is mathematically 
defined, we wouldn’t have this problem.

[toc] | [prev] | [next] | [standalone]

#4127

On 13.05.2011 12:55, Lawrence D'Oliveiro wrote:
> In message<dddba9e2-7e37-4fa4-
> bcb2-2e575deda114@w21g2000yqm.googlegroups.com>, Robert Klemme wrote:
>
>> On 10 Mai, 03:50, Lawrence D'Oliveiro<l...@geek-
>> central.gen.new_zealand>  wrote:
>>
>>> In message<92r02mF24...@mid.individual.net>, Robert Klemme wrote:
>>>
>>>> That's called an "inline view".
>>>
>>> The only reason there’s a special term for it is because SQL doesn’t
>>> treat tables/views as first-class objects.
>>
>> There are no objects in SQL.  SQL is neither procedural nor object
>> oriented.
>
> I didn’t say it was “object-oriented”. In Computer Science, we use the term
> “first-class object” to refer to what you can do with objects, not object-
> orientation<http://en.wikipedia.org/wiki/First_class_object>.

That article speaks about programming languages.  Since SQL isn't a 
programming languages strictly speaking the definition does not apply - 
or at least it has to be applied with a large grain of salt.

For example, since SQL does not have variables, you can never store 
anything there - hence there can be no first class objects at all in SQL.

You would have to update the term "FCO" for application in a declarative 
language as SQL before we can even discuss about whether there are FCOs 
in SQL or not - let alone in which ways SQL could be improved by making 
tables and views FCOs.

>> It is a declarative language implementing relational
>> algebra.
>
> If it DID implement proper relational algebra as it is mathematically
> defined, we wouldn’t have this problem.

I won't debate whether SQL does or does not implement relational algebra 
properly because obviously it does implement a reasonable subset of it 
(plus a few things more) and the term "probably" leaves too much room 
for personal taste and interpretation.

Remember, the whole discussion started when you said that the term 
"inline view" is only necessary because tables and views were no FCO in 
SQL and you claimed that this deficiency could be remedied by bringing 
more orthogonality into the language.  Up to now neither I nor 
apparently others (see Arved's recent posting) have been informed what 
it is that you find deficient and want to change about SQL.

Where's the beef?  As long as you don't deliver and tell us which 
particular deficiencies you see in SQL and how they can be remedied by 
adding orthogonality, I cannot take your talk about deficiencies serious.

Cheers

	robert


PS: The term "inline view" is necessary simply to distinguish a view 
defined in a query from a view defined in the data dictionary where it 
is given a name.  That's plain reasonable, and it happens all the time 
with language (our cars have four "wheels" and a "spare wheel"). 
Nothing deficient or weird about that.  We give different things 
different names all the time so we can distinguish them.

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

[toc] | [prev] | [next] | [standalone]

#3907

On 11-05-09 10:50 PM, Lawrence D'Oliveiro wrote:
> In message <92r02mF24dU1@mid.individual.net>, Robert Klemme wrote:
> 
>> That's called an "inline view".
> 
> The only reason there’s a special term for it is because SQL doesn’t treat 
> tables/views as first-class objects.

Using 'object' in 'first-class object' in the abstract sense - as
opposed to the narrower definition of 'object' in OOP - I find it
difficult to see how first-class objects aren't involved in modern SQL.

Leaving aside the fact that we have imperfect relations in most SQL
databases, the issue at hand is not whether tables/views are first-class
objects but whether _relations_ are.

Inline views are actually one example of runtime creation of relations.
We can store relations as tables. And the simple SELECT clause itself,
where tables/subqueries/inline views etc feature as input, and another
relation is produced, exhibits relations as both parameters and return
values.

It's close enough that I'm not losing sleep over it. Of more relevance,
how are _your_ forays into SQL hampered by the supposed fact that
tables/views aren't FCOs?

> This is an example of a situation where a little bit more orthogonality 
> could have made the language much simpler.

There's a statement that could mean just about anything. Which means
that it means nothing, without qualification.

If you mean orthogonality as in this quote taken from Michael Scott:

"Orthogonality means that features can be used in any combination, that
the combinations all make sense, and that the meaning of a given feature
is consistent, regardless of the other features with which it is combined".

then practically no programming language, SQL included, is highly
orthogonal. So there's always room for change. But often the lack of
this kind of orthogonality is because the programming language also has
to be pragmatic.

AHS

[toc] | [prev] | [next] | [standalone]

#4048

In message <rF7yp.70223$yp3.29927@newsfe09.iad>, Arved Sandstrom wrote:

> On 11-05-09 10:50 PM, Lawrence D'Oliveiro wrote:
> 
>> This is an example of a situation where a little bit more orthogonality
>> could have made the language much simpler.
>
> If you mean orthogonality as in this quote taken from Michael Scott:
> 
> "Orthogonality means that features can be used in any combination, that
> the combinations all make sense, and that the meaning of a given feature
> is consistent, regardless of the other features with which it is
> combined".
> 
> then practically no programming language, SQL included, is highly
> orthogonal.

I notice your weasel word “highly” there. The point is there are places 
where, as I said, a little bit more orthogonality could have made the 
language much simpler.

[toc] | [prev] | [next] | [standalone]

#4049

Lawrence D'Oliveiro wrote:
> Arved Sandstrom wrote:
>> Lawrence D'Oliveiro wrote:
>>> This is an example of a situation where a little bit more orthogonality
>>> could have made the language much simpler.

>> If you mean orthogonality as in this quote taken from Michael Scott:
>>
>> "Orthogonality means that features can be used in any combination, that
>> the combinations all make sense, and that the meaning of a given feature
>> is consistent, regardless of the other features with which it is
>> combined".
>>
>> then practically no programming language, SQL included, is highly
>> orthogonal.

> I notice your weasel word “highly” there. The point is there are places
> where, as I said, a little bit more orthogonality could have made the
> language much simpler.

Such as?

I'm sure you have details in mind, but you have a habit of making sweepingly 
general pronouncements without sharing any details.  That has the effect of 
making those sweeping pronouncements seem like nonsense.  Put in a few facts 
and some reasoning to connect them to your oracular insights, hm?

-- 
Lew
Honi soit qui mal y pense.
http://upload.wikimedia.org/wikipedia/commons/c/cf/Friz.jpg

[toc] | [prev] | [next] | [standalone]

#4125

On 11-05-13 07:57 AM, Lawrence D'Oliveiro wrote:
> In message <rF7yp.70223$yp3.29927@newsfe09.iad>, Arved Sandstrom wrote:
> 
>> On 11-05-09 10:50 PM, Lawrence D'Oliveiro wrote:
>>
>>> This is an example of a situation where a little bit more orthogonality
>>> could have made the language much simpler.
>>
>> If you mean orthogonality as in this quote taken from Michael Scott:
>>
>> "Orthogonality means that features can be used in any combination, that
>> the combinations all make sense, and that the meaning of a given feature
>> is consistent, regardless of the other features with which it is
>> combined".
>>
>> then practically no programming language, SQL included, is highly
>> orthogonal.
> 
> I notice your weasel word “highly” there. The point is there are places 
> where, as I said, a little bit more orthogonality could have made the 
> language much simpler.

Not exactly a "weasel word", given all that other commentary I
made...the stuff you snipped out of your reply. Fact is that relations
in SQL are pretty good imitations of FCOs. To your specific allegation,
that SQL does not treat base and derived relvars as FCOs, given the
manipulation that one _can_ do in SQL with tables and views, I'm
wondering what it is that you think is so important?

Bear in mind that what really matters is what you can do with relvars in
_SQL_. If you're quibbling about what you can, or cannot do, with
relvars outside of SQL, as for example with stored procs, then say so,
and don't bitch about SQL.

AHS

[toc] | [prev] | [next] | [standalone]

#4128

On 15.05.2011 18:09, Arved Sandstrom wrote:

> Fact is that relations
> in SQL are pretty good imitations of FCOs. To your specific allegation,
> that SQL does not treat base and derived relvars as FCOs, given the
> manipulation that one _can_ do in SQL with tables and views, I'm
> wondering what it is that you think is so important?

We all do and I have serious doubts that Lawrence will show the beef. 
So far it's just unsubstantiated opinion IMHO.

Cheers

	robert

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

[toc] | [prev] | [next] | [standalone]

Page 1 of 2  [1] 2  Next page →

Back to top | Article view | comp.lang.java.programmer

csiph-web