[GENERAL] order by question

2014-08-07 Thread Steve Clark

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

2014-08-07 Thread Kevin Grittner
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

2014-08-07 Thread Steve Clark

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

2014-08-07 Thread Kevin Grittner
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

2008-11-26 Thread Kevin Kempter
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

2008-11-26 Thread Grzegorz Jaśkiewicz
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

2008-11-26 Thread Andreas Kretschmer
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

2008-11-26 Thread Thomas Kellerer

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