On 7/25/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
>
> Why? What are you trying to achieve?
I am implementing a technique that sorts a result set according to weight
annotations in the WHERE.
The query
SELECT * FROM cars
WHERE (cdChanger=1){2}
OR (mp3player=1){1}
would be sorted according to partial conditions that hold.
Cars that have both a CD changer AND a MP3 player get a weight of 3, i.e. (2+1).
Cars that only have a CD changer get a weight of 2.
Cars that only have a MP3 player get a weight of 1.
Hmm, any particular reason why not doing it this way: ?
SELECT * FROM cars
WHERE cdChanger=1 OR mp3player=1
ORDER BY CASE WHEN cdChanger=1 THEN 2 ELSE 0 END
+ CASE WHEN mp3player=1 THEN 1 ELSE 0 END DESC;
...perhaps wrapping the CASE into something like:
CREATE FUNCTION weight_if(boolean,int) RETURNS int AS $$ SELECT CASE
WHEN $1 THEN $2 ELSE 0 END $$ IMMUTABLE LANGUAGE SQL;
...and using it like:
SELECT * FROM cars
WHERE cdChanger=1 OR mp3player=1
ORDER BY weight_if(cdChanger=1,2) + weight_if(mp3player=1, 1) DESC;
Regards,
Dawid
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
