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:
> 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?
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