Postgres Pro
Downloads
Home   >   mailing lists

Thread: Result of ORDER-BY

Result of ORDER-BY

From
"Good Day Books"
Date:
[PostgreSQL 8.3.9]

I have a query, as follows

SELECT DISTINCT ON(category) category
FROM gdb_books
WHERE category LIKE 'Fiction%'
GROUP BY category

The (partial) result is this:
...
# Fiction - General (A)
# Fiction - General - Anthologies
# Fiction - General (B)
# Fiction - General (C)
# Fiction - General (D)
...

I would have expected '- Anthologies' to be either at the top, or at the bottom of the result.

Does anyone have an explanation why this is not so; are the special characters (parenthesis, hyphen) just ignored?  If
so,is there a way to force ORDER BY to include the special characters in the sort? 

Thank you for any reply.
Pat Willener
GDB Tokyo

Re: Result of ORDER-BY

From
Christophe Pettus
Date:
On Nov 17, 2011, at 7:14 PM, Good Day Books wrote:

> Does anyone have an explanation why this is not so; are the special characters (parenthesis, hyphen) just ignored?
Ifso, is there a way to force ORDER BY to include the special characters in the sort? 

The query as shown does't actually have an ORDER BY clause in it; did you write GROUP BY where you meant ORDER BY?

--
-- Christophe Pettus
   xof@thebuild.com

Re: Result of ORDER-BY

From
Andrew Sullivan
Date:
On Fri, Nov 18, 2011 at 12:14:35PM +0900, Good Day Books wrote:
> [PostgreSQL 8.3.9]
>
> I have a query, as follows
>
> SELECT DISTINCT ON(category) category
> FROM gdb_books
> WHERE category LIKE 'Fiction%'
> GROUP BY category
>
> Does anyone have an explanation why this is not so; are the special characters (parenthesis, hyphen) just ignored?
Ifso, is there a way to force ORDER BY to include the special characters in the sort? 
>

See the other remark in this thread about GROUP BY and ORDER BY.  Note
that GROUP BY used to cause ORDER BY every time, because it was always
implemented with a sort.  That hasn't been true for several releases,
and if you're relying on that side effect it could be the cause of
this, although it's pretty surprising that you still got A, B, C in
that case.  In any case, you definitely need an ORDER BY category
here, too.  Does that make a difference?

You might also want to look at your collation.  Sort orders are
notorious for being surprising across collations.  What's this one?

A

--
Andrew Sullivan
ajs@crankycanuck.ca

Re: Result of ORDER-BY

From
"Good Day Books"
Date:
> See the other remark in this thread about GROUP BY and ORDER BY.  Note
> that GROUP BY used to cause ORDER BY every time, because it was always
> implemented with a sort.  That hasn't been true for several releases,
> and if you're relying on that side effect it could be the cause of
> this, although it's pretty surprising that you still got A, B, C in
> that case.  In any case, you definitely need an ORDER BY category
> here, too.  Does that make a difference?
>
> You might also want to look at your collation.  Sort orders are
> notorious for being surprising across collations.  What's this one?

Thank you very much for your reply. Yes, I tried ORDER BY, and the result
is the same.

I am not sure about collations; I guess I will have to read up on this
subject.

Re: Result of ORDER-BY

From
"Good Day Books"
Date:
> The query as shown does't actually have an ORDER BY clause in it;
> did you write GROUP BY where you meant ORDER BY?

Thank you for your reply. I tried all combinations
- GROUP BY
- ORDER BY
- GROUP BY & ORDER BY
the result is always the same.