Re: Is this query optimized?

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 ? Received on Sat Jun 13 2009 - 20:38:46 CEST