473,382 Members | 1,354 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,382 software developers and data experts.

Sort order confusion

I am suffering some sort order confusion. Given a database, "foo",
with a single character(4) column of data left padded with spaces I
get:

select * from foo order by somechars;

somechars
-----------
0
1
10
100
1011
111
1512
2
222
3
333

The output I desire is a basic ASCII sort:

somechars
-----------
0
1
2
3
10
100
111
222
333
1011
1512

(This sample set just has spaces and numeric digits but could have
other characters - I want the output in ASCII sort order)

1. What is the correct way to do this?

2. How do I verify the locale setting of an existing database cluster
(to verify that I really initialized it as "initdb -d --locale=C -D
/var/lib/pgsql/data")?

3. Should I have included "--enable-locale" or similar option when I
built Postgresql (the build is vanilla 7.4.1 "./configure ; make ;
make install")?

Cheers,
Steve
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 22 '05 #1
7 7504
On Fri, 2004-02-06 at 11:12, Steve Crawford wrote:
I am suffering some sort order confusion. Given a database, "foo",
with a single character(4) column of data left padded with spaces I
get:

select * from foo order by somechars;

somechars
-----------
0
1
10
100
1011
111
1512
2
222
3
333

The output I desire is a basic ASCII sort:

somechars
-----------
0
1
2
3
10
100
111
222
333
1011
1512

(This sample set just has spaces and numeric digits but could have
other characters - I want the output in ASCII sort order)


Your original sort is a basic lexigraphic ("alphabetical" by ASCII
character set number) sort.

What you appear to want is a numeric sort, where the numbers come out in
the order of numbers, rather than in their ASCII character set order.

If there were just digits that would be fairly easy, but I can't see any
way (short of post-processing the list in other software or writing
yourself a stored procedure) to do it in SQL.

Stephen

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQBAIt4V4hFS2REFUecRAkPYAJ0dUnhZDthau4u3rlY2u/Zu6ndbMQCginci
zu1Wv3XR0KtNK85NkonbciY=
=VAoe
-----END PGP SIGNATURE-----

Nov 22 '05 #2
On Thursday 05 February 2004 4:21 pm, Stephen Robert Norris wrote:
On Fri, 2004-02-06 at 11:12, Steve Crawford wrote:
I am suffering some sort order confusion. Given a database,
"foo", with a single character(4) column of data left padded with
spaces I get:

select * from foo order by somechars;

somechars
-----------
0
1
10
100
1011
111
1512
2
222
3
333
<snip>

Your original sort is a basic lexigraphic ("alphabetical" by ASCII
character set number) sort.

What you appear to want is a numeric sort, where the numbers come
out in the order of numbers, rather than in their ASCII character
set order.


Not exactly. I _DO_ want it in ASCII character set order which
includes spaces (0x20) sorting ahead of digits (0x30 - 0x39). This is
not what is happening. The first sort is some SQL sort order that
seems to ignore certain characters. Note the different sort order if
I pad with 'x' instead of '<space>':

somechars
-----------
1011
1512
x100
x111
x222
x333
x444
x555
x666
x777
x888
xx10
xx44
xx55
xxx0
xxx1
xxx2
xxx3
xxx4
xxx5
xxx6
xxx7
xxx8
xxx9
xxxx

Naturally if I were dealing with fields guaranteed to have something
that would convert to an int I could just order by, say,
int4(somechars) but that is not the case.

I even tried the to_ascii function but apparently that's the wrong
approach:
ERROR: encoding conversion from SQL_ASCII to ASCII not supported

Cheers,
Steve
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 22 '05 #3
On Fri, Feb 06, 2004 at 11:21:41AM +1100, Stephen Robert Norris wrote:
On Fri, 2004-02-06 at 11:12, Steve Crawford wrote:
I am suffering some sort order confusion. Given a database, "foo",
with a single character(4) column of data left padded with spaces I
get:

select * from foo order by somechars;


Cast it:

alvherre=> select * from foo order by bar::text::int;
bar
------
0
1
10
33
100
101
333
503
(8 filas)

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"La naturaleza, tan frágil, tan expuesta a la muerte... y tan viva"

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 22 '05 #4
On Thursday 05 February 2004 5:01 pm, Alvaro Herrera wrote:
On Fri, Feb 06, 2004 at 11:21:41AM +1100, Stephen Robert Norris

wrote:
On Fri, 2004-02-06 at 11:12, Steve Crawford wrote:
I am suffering some sort order confusion. Given a database,
"foo", with a single character(4) column of data left padded
with spaces I get:

select * from foo order by somechars;


Cast it:

alvherre=> select * from foo order by bar::text::int;
bar
------
0
1
10
33
100
101
333
503
(8 filas)


Can't. As noted in the original post the column may contain data that
won't convert to an int (all spaces, characters, punctuation). I'm
seeking generic true ASCII sort order.

Cheers,
Steve
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 22 '05 #5
On Thu, 5 Feb 2004, Steve Crawford wrote:
2. How do I verify the locale setting of an existing database cluster
(to verify that I really initialized it as "initdb -d --locale=C -D
/var/lib/pgsql/data")?


I think it'd be
pg_controldata /var/lib/pgsql/data
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 22 '05 #6
Steve Crawford <sc*******@pinpointresearch.com> writes:
Not exactly. I _DO_ want it in ASCII character set order which
includes spaces (0x20) sorting ahead of digits (0x30 - 0x39). This is
not what is happening. The first sort is some SQL sort order that
seems to ignore certain characters.


Sounds to me like you've got the database in a non-C locale. See past
discussions ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 22 '05 #7
On Thursday 05 February 2004 6:08 pm, Tom Lane wrote:
Steve Crawford <sc*******@pinpointresearch.com> writes:
Not exactly. I _DO_ want it in ASCII character set order which
includes spaces (0x20) sorting ahead of digits (0x30 - 0x39).
This is not what is happening. The first sort is some SQL sort
order that seems to ignore certain characters.


Sounds to me like you've got the database in a non-C locale. See
past discussions ...


That was my first inclination (as noted in the full version of my
original post - now lost to the thread) but my installation history
showed:
initdb -d --locale=C -D /var/lib/pgsql/data

Nonetheless pg_controldata shows:
LC_COLLATE: en_US
LC_CTYPE: en_US

I now suspect that currently active "real" database was in-fact
created not by my several test initializations but by the SuSE
startup script which will run an "initdb" if the database has not
been initialized and which reads /etc/sysconfig/language to get
default locale settings.

So...

Is there a method of changing the locale of an extant database or do I
need to dump ; delete db ; reinit ; restore ?

Cheers,
Steve
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 22 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: its me | last post by:
Let's say I have a class of people... Public Class People Public Sex as String Public Age as int Public Name as string end class And I declare an array of this class...
6
by: alexhong2001 | last post by:
Does "std::sort" work only with sequence containers, not associative containers at all? Among sequential containers, can it be used with "list", "queue" and other sequence containers besides...
20
by: Xah Lee | last post by:
Sort a List Xah Lee, 200510 In this page, we show how to sort a list in Python & Perl and also discuss some math of sort. To sort a list in Python, use the “sort” method. For example: ...
11
by: James P. | last post by:
Hello, I have a report with the Priority field is used as sort order and grouping. The problem is the data in this Priority field if sorted in ascending order is: High, Low, and Medium. How...
5
by: TM | last post by:
I am using an access database in my vb.net application and it is tied to a datagrid. My problem is that the field I want to sort on is a text field, 5 characters long, and it contains not only...
6
by: Julian | last post by:
Hi, I am a very beginner in databases. I created a database table in Access 2003 and OOo 2.03 that includes name, address, postcode, phone numbers etc of our customers. I would like to sort...
2
by: Randy | last post by:
I have two listboxes on a form. The first box displays categories while the second box displays the items belonging to the category selected in the first box. Thus, the second box is essentially...
3
by: aRTx | last post by:
I have try a couple of time but does not work for me My files everytime are sortet by NAME. I want to Sort my files by Date-desc. Can anyone help me to do it? The Script <? /* ORIGJINALI
7
by: otis | last post by:
Hi all, This is a small issue to make things prettier, but we all know how important that can be! I had an xsl:if to check if a node was the last one in a collection of nodes and if it was a...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.