To: "Peter Galbavy" <peter.galbavy@knowtion.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: weighting (the results of) a query ? 
In-reply-to: <021001c2b666$752c1d60$4528a8c0@cblan.mblox.com> 
References: <001301c2b4a6$f969b360$36bd10ac@walrus>
	<021001c2b666$752c1d60$4528a8c0@cblan.mblox.com>
Comments: In-reply-to "Peter Galbavy" <peter.galbavy@knowtion.net>
	message dated "Tue, 07 Jan 2003 16:04:28 +0000"
Date: Tue, 07 Jan 2003 11:14:30 -0500
Message-ID: <13705.1041956070@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"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