Is this query optimized?

[lawpoop]

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?

[lawpoop]

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?

[Bob Badour]

lawpoop wrote:

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

[toby]

What about ORDER BY GREATEST(r,g,b) DESC, r+g+b DESC ?

[Bob Badour]

toby wrote:

GREATEST doesn't work, but LEAST might do; assuming GREATEST and LEAST
are available.

[--CELKO--]

Why did you write in SQL Server dialect? You are posting in the
wrong newsgroup. Here is one that will work in SQL Server 2008, DB2,
Oracle, etc and probably perfrom well.

WITH
SELECT clr_nbr
FROM (VALUES (0), (64), (128), (192), (255))
AS Colors (clr_nbr)

SELECT R.clr_nbr AS red, G.clr_nbr AS green, B.clr_nbr AS blue
FROM Colors AS R, Colors AS G, Colors AS B;