Result of ORDER-BY
[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
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? If so, 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
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 categoryDoes 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?
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
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.
Import Notes
Reply to msg id not found: | Resolved by subject fallback
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.
Import Notes
Reply to msg id not found: | Resolved by subject fallback