Re: Is this query optimized?

From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Sat, 13 Jun 2009 15:48:38 -0300
Message-ID: <4a33f48d$0$23785$9a566e8b_at_news.aliant.net>

toby wrote:

> On Jun 12, 4:11 pm, lawpoop <lawp..._at_gmail.com> wrote:
> 

>>On Jun 12, 3:27 pm, lawpoop <lawp..._at_gmail.com> wrote:
>>
>>
>>>I want to make an array of RGB colors. For the set ( 0, 64, 128, 192,
>>>255 ), I want to have each combination thereof ( the Cartesian
>>>product? ) .
>>
>>>This is the query I have to do it:
>>
>>>SELECT r, g, b FROM
>>>( SELECT 0 AS r UNION SELECT 64 AS r UNION SELECT 128 AS r UNION
>>>SELECT 192 AS r UNION SELECT 255 AS r ) AS r
>>>,( SELECT 0 AS g UNION SELECT 64 AS g UNION SELECT 128 AS g UNION
>>>SELECT 192 AS g UNION SELECT 255 AS g ) AS g
>>>,( SELECT 0 AS b UNION SELECT 64 AS b UNION SELECT 128 AS b UNION
>>>SELECT 192 AS b UNION SELECT 255 AS b ) AS b
>>
>>>Is there a simpler way?
>>
>>Suppose this is optimize -- or, we're going to use it regardless.
>>Futhermore, suppose I want to have a different order of the rows.
>>
>>Adding
>>
>>ORDER BY r DESC, g DESC, b DESC
>>
>>gives me a result like
>>r g b
>>255 255 255
>>255 255 192
>>255 255 128
>>255 255 64
>>255 255 0
>>255 192 255
>>255 192 192
>>255 192 128
>>255 192 64
>>...
>>
>>But, I want
>>r g b
>>255 255 255
>>255 255 192
>>255 192 255
>>192 255 255
>>192 192 255
>>192 255 192
>>255 192 192
>>192 192 192
>>
>>Actually, I'm not necessarily interested in that order per se; but
>>rather I want the higher numbers to appear first in the record set.
>>
>>Now that I've taken the time to try to express this logically, I can
>>see that the sort order that I want is actually by the sum of the
>>columns. I can do
>>
>>SELECT r, g, b, r+b+g AS sort_order FROM
>>( SELECT 0 AS r UNION SELECT 64 AS r UNION SELECT 128 AS r UNION
>>SELECT 192 AS r UNION SELECT 255 AS r ) AS r
>>,( SELECT 0 AS g UNION SELECT 64 AS g UNION SELECT 128 AS g UNION
>>SELECT 192 AS g UNION SELECT 255 AS g ) AS g
>>,( SELECT 0 AS b UNION SELECT 64 AS b UNION SELECT 128 AS b UNION
>>SELECT 192 AS b UNION SELECT 255 AS b ) AS b
>>ORDER BY sort_order DESC
>>
>>And get
>>255 255 255 765
>>192 255 255 702
>>255 192 255 702
>>255 255 192 702
>>255 192 192 639
>>192 192 255 639
>>192 255 192 639
>>255 128 255 638 *
>>
>>Well, that breaks down at the asterisk. I want any order of ( 192,
>>192, 192 ) to come before ( 255, 255, 128 ), but my 'sort by sum'
>>won't work, because the sum of the 192 set is less than the sum of the
>>( 255, 255, 128 ) set.
>>
>>My initial thought starts are leaning towards more alias tables to get
>>what I want. Am I going down the right track?
>
> What about ORDER BY GREATEST(r,g,b) DESC, r+g+b DESC ?

GREATEST doesn't work, but LEAST might do; assuming GREATEST and LEAST are available. Received on Sat Jun 13 2009 - 20:48:38 CEST