# Re: Is this query optimized?

Date: Fri, 12 Jun 2009 17:50:28 -0300
Message-ID: <4a32bf9a\$0\$23772\$9a566e8b_at_news.aliant.net>

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

The order you want will require a case/when expression. You need to ask yourself where you want (128,192,255) to appear. I am not sure what you mean by alias tables or what you hope to accomplish with them.

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 ORDER BY case when r<b and r<g then r when b<g then b else g end DESC, r+b+g desc

or

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 ORDER BY case when r>b and r>g then

r
when b>g then

b
else

g
end DESC
, case when r>b and r>g and b>g then

b
when r>b and r>g then

g
when b>g and g>r then

g
when b>g then

r
else b>r then

b
else

r
end desc
, r+b+g desc Received on Fri Jun 12 2009 - 22:50:28 CEST

Original text of this message