Re: [SQL] "correct" sorting.

[Jeff Self]

You're trying to compare apples and oranges. Since the field is of type
text, it will sort by text. Therefore, the result will be 1 then 10 then
1a and so forth. It is sorting based on ASCII. The only way to get it to
sort in proper numerical order is to make the field a numeric field. But
of course you won't be able to use characters in that. Therefore, create a
second field called revision or whatever of text. Now you can sort
correctly with:

select * from foo order by var1,revision;


 On Thu, 3 May 2001, Jeff
MacDonald wrote:

> Hi folks,
>
> say i have a text field with teh values
>
> 1,2,3,10,20,30,1a,1b,2a,2b
>
> and i want to sort it so i get,
>
> 1
> 1a
> 1b
> 2
> 2a
> 2b
> 3
> 10
> 20
> 30
>
> is there anyway to do that with postgresql ?
> below is what actually happens.
>
> jeff=> select * from foo order by var1;
>  var1
> --
>  1
>  10
>  1a
>  1b
>  2
>  20
>  2a
>  2b
>  3
>  30
>  3a
>  3b
> (12 rows)
>

-- 
Jeff Self
Information Specialist
Great Bridge, LLC
www.greatbridge.com | www.greatbridge.org
Norfolk, VA
(757)233-5570
[EMAIL PROTECTED]


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[SQL] Is there something wrong with Perl's dbi and PostgreSQL?

[Jeff Self]

I just tried running a perl script that I wrote about a year ago. It had
worked for me in the past. Now it doesn't work. I can view data from a
table, but I am unable to either drop a table or create a table. I'm
running PostgreSQL 7.3.4 and DBI 1.35 along with Perl 5.8.1.

Here's my script for creating a table:
#!/usr/bin/perl -w
# createfoo.pl

use strict;
use DBI;

my $dbh = DBI->connect( 'dbi:Pg:dbname=test','dbuser','password',
  {
RaiseError => 1,
AutoCommit => 0
  }
  ) || die "Database connection not made:
$DBI::errstr";

my $query = $dbh->prepare(
"CREATE TABLE foo (foo_id INT4," .
"foo_name TEXT)"
);
$query->execute();
$dbh->disconnect();


Has anything changed to DBI? I haven't found anything on google
suggesting that it has.

-- 
Jeff Self
Dept. of Information Technology
City of Newport News
(757)926-3741


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

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