I think the source of the problem here is that the field you're using to
order by (page_no) is not actually an output field of the query. I'm
guessing that, when it finds the reference to page_no, the parser is
implicitly deciding it has to replace the "DISTINCT articles.id" with
"DISTINCT *" if it is to have any chance at doing the required sorting.
Personally, this interpretation doesn't really make sense to me (I'd have
thought it should trigger a syntax error).
Anyway, back to getting a sensible answer from your query. As it stands, I
think the query is ambiguous. If 'dementia' appears on a number of pages in
a given article, how do you want the results to be ordered? The two main
choices seem to be:
1. Simply order by article id number, in which case substituting "ORDER BY
articles.id" in the original query will suffice.
2. Order by page number of first appearance (say), in which case a query
like this should work:
SELECT articles.id, MIN(articles.page_no) AS page_no FROM articles,
paragraphs WHERE paragraphs.article_key = articles.id and
paragraphs.paragraph ~* 'dementia' GROUP BY articles.id ORDER BY
MIN(articles.page_no);
Hope that helps.
Sean.
> -----Original Message-----
> From: Behalf Of Tim Joyce
> Sent: Tuesday, 1 February 2000 6:44
> To: PGSQL
> Subject: [GENERAL] select distinct
>
>
> Hi,
>
> I am running this query and expecting distinct results
>
> ebmh=> SELECT DISTINCT articles.id FROM articles, paragraphs WHERE
> paragraphs.ar
> ticle_key = articles.id and paragraphs.paragraph ~* 'dementia' ORDER BY
> articles
> .page_no;
> id
> --
> 4
> 4
> 4
> 4
> 16
> 12
> 12
> 12
> 12
> 12
> 3
> 3
> 3
> (13 rows)
>
> however, if i remove the ORDER BY, it works
>
> ebmh=> SELECT DISTINCT articles.id FROM articles, paragraphs WHERE
> paragraphs.ar
> ticle_key = articles.id and paragraphs.paragraph ~* 'dementia';
> id
> --
> 3
> 4
> 12
> 16
> (4 rows)
>
> this is in 6.4 and 6.5.2, am i doing something wrong?
>
> Cheers
>
> Tim Joyce
> Chief Enthusiast
> tim@hoop.co.uk
> HOOP Ltd
> http://www.hoop.co.uk
> 01202 251 816
>
> HOOP is proud to be a member of the Paneris community (www.paneris.co.uk)
>
>
>
>
>
> ************
>
>