Ordering 'A', 'B', ..., 'Z', 'AA', 'AB', ...

On Wed, 20 Feb 2002, Jon Lapham wrote:

...

What I would like to to return the tuples in this order:
A
B
C
...
X
Y
Z
AA
AB
...

Instead of:
A
AA
AB
...
B
BA
BB

In Perl you can do it with 3 lines of code. Consider the example
below:

#!/usr/bin/perl -w
use strict;

my @testset = ( 'A'..'Z' );
push @testset, ( 'AA'..'ZZ' );

for ( sort @testset ) {
print "DEFAULT ORDERING: $_\n";
}

for ( sort { length($a) <=> length($b)
||
$a cmp $b } @testset ) {
print "ORDERING USER DEFINED: $_\n";
}

;-)))
Best regards
Herbie
--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Herbert Liechti http://www.thinx.ch
ThinX networked business services Adlergasse 5, CH-4500 Solothurn
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

At 4:47 PM -0300 2/20/2002, Jon Lapham wrote:

Hello folks-

Does anyone have a suggestion on how to order a varchar(2) column
containing values like: 'A', 'B', ..., 'Z', 'AA', 'AB', ...

What I would like to to return the tuples in this order:
A
B
C
...
X
Y
Z
AA
AB
...

In transact-sql, I've used a structure something like:

ORDER BY
CASE WHEN TABLE.MYCOLUMN LIKE '__'
THEN TABLE.MYCOLUMN
ELSE ' ' || TABLE.MYCOLUMN END

The syntax may be a little different in pg, though.

It's also easy enough to do in Perl with a custom sort sub.

-Charles
--
Charles Albrecht Euonymic Solutions
charlesa@pobox.com P.O. Box 300623
C>303.619.7596 F>978.334.3061 Denver, Colorado 80203-0623
http://www.euonymic.net/~charlesa/cv/

ORDER BY
CASE WHEN TABLE.MYCOLUMN LIKE '__'
THEN TABLE.MYCOLUMN
ELSE ' ' || TABLE.MYCOLUMN END

The syntax may be a little different in pg, though.

It's also easy enough to do in Perl with a custom sort sub.

In PostgreSQL, you would need to create a column with the CASE, the
order by that using the column number or AS to name the column and order
on that name.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

In Perl you can do it with 3 lines of code. Consider the example
below:

#!/usr/bin/perl -w
use strict;

my @testset = ( 'A'..'Z' );
push @testset, ( 'AA'..'ZZ' );

for ( sort @testset ) {
print "DEFAULT ORDERING: $_\n";
}

for ( sort { length($a) <=> length($b)
||
$a cmp $b } @testset ) {
print "ORDERING USER DEFINED: $_\n";
}

This should work:
select * from mytable order by length(myfield),myfield;

Jon,
How about "order by char_length(field), field"
Thanks,
Peter Darley

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Jon Lapham
Sent: Wednesday, February 20, 2002 11:47 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Ordering 'A', 'B', ..., 'Z', 'AA', 'AB', ...

Hello folks-

Does anyone have a suggestion on how to order a varchar(2) column
containing values like: 'A', 'B', ..., 'Z', 'AA', 'AB', ...

What I would like to to return the tuples in this order:
A
B
C
...
X
Y
Z
AA
AB
...

Instead of:
A
AA
AB
...
B
BA
BB
...

While I would prefer to do the ordering in SQL, my application is
written perl, so I could always do the ordering perl-ically.

Thanks in advance!

--

-**-*-*---*-*---*-*---*-----*-*-----*---*-*---*-----*-----*-*-----*---
Jon Lapham
Extracta Mol�culas Naturais, Rio de Janeiro, Brasil
email: lapham@extracta.com.br web: http://www.extracta.com.br/
***-*--*----*-------*------------*--------------------*---------------

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

http://archives.postgresql.org