Re: weighting (the results of) a query ? - Mailing list pgsql-sql

"Peter Galbavy" <peter.galbavy@knowtion.net> writes:
> Thanks to Len Morgan for the hints to get to this:
> SELECT *, '4' as result  FROM images
> WHERE
>         iptc_release_date < 'today' AND
>         iptc_priority BETWEEN 1 AND 5 AND iptc_caption ~* 'term'
> UNION
> SELECT *, '3' as result FROM images
> WHERE
>         iptc_release_date < 'today' AND
>         iptc_priority BETWEEN 1 AND 5 AND iptc_keywords ~* 'term'
> UNION
> SELECT *, '2' as result FROM images
> WHERE
>         iptc_release_date < 'today' AND
>         iptc_priority BETWEEN 1 AND 5 AND iptc_category ~* 'term'
> UNION
> SELECT *, '1' as result FROM images
> WHERE
>         iptc_release_date < 'today' AND
>         iptc_priority BETWEEN 1 AND 5 AND iptc_subcategory ~* 'term'

> ORDER BY result desc, iptc_priority asc, shot_time, image_serial asc;

> Using a constant and UNION made it work OK. Not sure yet on real world
> performance, but that's what tuning is for :)

Most likely you should write UNION ALL, not UNION.  As given, the query
will go through a pass of attempted duplicate-row-elimination, which is
almost certainly not what you want.
        regards, tom lane