"Peter Galbavy" <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
