[MI-L] SUM: Sorting records by val of a character formatted column

David Reid Sat, 29 Jul 2006 13:21:49 -0700

Title: Message

Many thanks Peter and Greg. I had the syntax correct but wasn't structuring my query properly.
(using val() to tell the sort to numerically value the character formatted numerals)

One cannot use an _expression_ like this in the "Order by" field alone, but can by means of a "derived" column in the "Select columns" box, then ordring by the derived column.

This actually saved an additional step I was taking in that I initially had queried out these parcel "lots" by their subdivision name into a query browser, then was attempting to sort that query. With the above solution, i merely added the "where condition" to the cocktail thus selecting AND sorting the lots for only the area I wanted in one swoop.

Thanks again!

Dave

-----Original Message-----
From: Peter Horsbøll Møller [mailto:[EMAIL PROTECTED]
Sent: Friday, July 28, 2006 3:00 AM
To: David Reid; [email protected]
Subject: RE: [MI-L] Sorting records by val of a character formatted column

Dave,

I think you have found the right method, but you might not be able to sort by an _expression_ directly, so you would need to add the _expression_ to the list of columns to select and then order by this new column.

Select LOTNBR, Val(LOTNBR) "LOTNBR_VAL"
From LOTS
Order By LOTNBR_VAL

Peter Horsbøll Møller
GIS Developer, MTM
Geographical Information & IT

COWI A/S
Odensevej 95
DK-5260 Odense S.
Denmark

Tel     +45 6311 4900
Direct +45 6311 4908
Mob     +45 5156 1045
Fax     +45 6311 4949
E-mail [EMAIL PROTECTED]
http://www.cowi.dk/gis

"For enden af regnbuen..." - hvordan kommer man dertil og er det overhovedet muligt?
Læs mere om årets MapInfo konference på www.cowi.dk/mapinfokonference

-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of David Reid

Sent: Friday, July 28, 2006 5:45 AM
To: [email protected]
Subject: [MI-L] Sorting records by val of a character formatted column

Greetings List,

I have a parcel table with the field [LOTNBR] formatted as "Character"

Naturally when sorted you get:

1
10
11
12
2
20
21 etc etc

I've tried using in the "sort" field val(LOTNBR) but to no avail to achieve:

1
2
3
4 etc

Anyone know the syntax to do this?

Thanks, Dave

--
Outgoing mail is certified Virus Free
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.10.4/401 - Release Date: 07/26/2006

_______________________________________________
MapInfo-L mailing list
[email protected]
http://www.directionsmag.com/mailman/listinfo/mapinfo-l

--
Outgoing mail is certified Virus Free
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.10.5/403 - Release Date: 07/28/2006

_______________________________________________
MapInfo-L mailing list
[email protected]
http://www.directionsmag.com/mailman/listinfo/mapinfo-l

[MI-L] SUM: Sorting records by val of a character formatted column

Reply via email to