Thread: 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/ ***-*--*----*-------*------------*--------------------*---------------
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/
> 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;
> 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
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
pgman wrote: > > 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. Sorry, I was wrong. You can use CASE in ORDER BY. I made a mistake in my quick testing. -- 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