[GENERAL] order by question
Hello, I am confused by how postgres 8,4..13 is sorting my data. \d test Table public.test Column | Type | Modifiers +--+--- data | text | select * from test order by data; data -- - -- 1 11 11F 1F a b C F -F Feneric Generic (14 rows) The first row is a single space, the next row a single -, the next two -- . What I don't understand is why the '-F', the ' Feneric' and the ' Generic' sort where they do. I would expect the output to be like this: data -- Feneric Generic - -- -F 1 11 11F 1F a b C F (14 rows) client_encoding - SQL_ASCII lc_collate - en_US.UTF-8 foxboxconfig=# show lc_ctype; lc_ctype - en_US.UTF-8 Thanks for any clarification. -- Stephen Clark -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] order by question
Steve Clark scl...@netwolves.com wrote: I am confused by how postgres 8,4..13 is sorting my data. select * from test order by data; data -- - -- 1 11 11F 1F a b C F -F Feneric Generic (14 rows) The first row is a single space, the next row a single -, the next two -- . What I don't understand is why the '-F', the ' Feneric' and the ' Generic' sort where they do. I would expect the output to be like this: data -- Feneric Generic - -- -F 1 11 11F 1F a b C F (14 rows) lc_collate - en_US.UTF-8 PostgreSQL uses the OS collations. What you are getting matches my Ubuntu 14.04 machine: kgrittn@Kevin-Desktop:~/pg/master$ echo $LANG en_US.UTF-8 kgrittn@Kevin-Desktop:~/pg/master$ sort XXX Feneric Generic - -- -F 1 11 11F 1F a b C F XXX - -- 1 11 11F 1F a b C F -F Feneric Generic -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] order by question
On 08/07/2014 11:36 AM, Kevin Grittner wrote: Steve Clark scl...@netwolves.com wrote: I am confused by how postgres 8,4..13 is sorting my data. select * from test order by data; data -- - -- 1 11 11F 1F a b C F -F Feneric Generic (14 rows) The first row is a single space, the next row a single -, the next two -- . What I don't understand is why the '-F', the ' Feneric' and the ' Generic' sort where they do. I would expect the output to be like this: data -- Feneric Generic - -- -F 1 11 11F 1F a b C F (14 rows) lc_collate - en_US.UTF-8 PostgreSQL uses the OS collations. What you are getting matches my Ubuntu 14.04 machine: kgrittn@Kevin-Desktop:~/pg/master$ echo $LANG en_US.UTF-8 kgrittn@Kevin-Desktop:~/pg/master$ sort XXX Feneric Generic - -- -F 1 11 11F 1F a b C F XXX - -- 1 11 11F 1F a b C F -F Feneric Generic -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company Hi Kevin, Thanks for the response. I get the same thing as postgres if I sort from the command line too. But I don't understand why. I would expect '-F' to sort immediately after '-' and '--' not after 'F' as well as ' Feneric' (spaceFeneric) and ' Generic' (spaceGeneric) to sort immediately after ' ' (space). It is like the space character and the - in -Letter is ignored. -- Stephen Clark -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] order by question
Steve Clark scl...@netwolves.com wrote: It is like the space character and the - in -Letter is ignored. Yes, that is how the en_US collation is defined. I think the goal is to make it something like phone book ordering. If you still have a white pages book around, look at how a business name with a hyphen (or a hyphenated last name) is sorted there. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Order by question
Hi All; I'm selecting 3 columns. I want to order the results ascending by col1 and col2 and then descending by col3 Whats the syntax for this? Thanks in advance -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Order by question
On Wed, Nov 26, 2008 at 7:46 PM, Kevin Kempter [EMAIL PROTECTED]wrote: Hi All; I'm selecting 3 columns. I want to order the results ascending by col1 and col2 and then descending by col3 Whats the syntax for this? select * from foo order by a,b asc, c desc; ? -- GJ
Re: [GENERAL] Order by question
Kevin Kempter [EMAIL PROTECTED] schrieb: Hi All; I'm selecting 3 columns. I want to order the results ascending by col1 and col2 and then descending by col3 Whats the syntax for this? order by col1 asc, col2 asc, col3 desc; Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) If I was god, I would recompile penguin with --enable-fly. (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Order by question
Kevin Kempter wrote on 26.11.2008 20:46: Hi All; I'm selecting 3 columns. I want to order the results ascending by col1 and col2 and then descending by col3 Whats the syntax for this? ORDER BY col1 ASC, col2 ASC, col3 DESC this is documented in the manual -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general