Re: Is this query optimized?

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

r 	g 	b
255 	255 	255
255 	255 	192
255 	192 	255
192 	255 	255
192 	192 	255
192 	255	192
255 	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? Received on Fri Jun 12 2009 - 22:11:47 CEST