Re: Design: Escort info from WHERE clause to executor?

On 7/25/07, peter.trautmeier@gmx.de <peter.trautmeier@gmx.de> 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 carsWHERE cdChanger=1 OR mp3player=1ORDER BY CASE WHEN cdChanger=1 THEN 2 ELSE 0 END           + CASE
WHENmp3player=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 carsWHERE cdChanger=1 OR mp3player=1ORDER BY weight_if(cdChanger=1,2) + weight_if(mp3player=1, 1) DESC;
  Regards,    Dawid