Re: Result of ORDER-BY

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? 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(at)crankycanuck(dot)ca