vbScript - A Multi-column Sort With Minimal Coding

vbScript - A Multi-column Sort With Minimal Coding

Please see my post vbScript - The Basics for more details on vbScript.

A previous post dealt with sorting. The code easily handles sorting simple things like lists of words, lines of text - cases where you only had to compare two things. What about the case where you want the option to sort on an arbitrary column of data, or you want to sort on multiple columns such as by birthdate, then by last name. That becomes more difficult to code.

Databases handle that with ease by allowing you to specify complex sorts in a SQL string. But sometimes you don't want to have to define a database just for the convenience of sorting.

In vbScript you can use ADO (ActiveX Data Objects) to create recordsets that are not attached to a database. You can add records, then sort them on the fly. You define a recordset by

Set rec = CreateObject("ADODB.RecordSet")

Then you add fields to the recordset with the Append method. You have access to all of the standard field type like VarChar, Int, Float, etc. I'll give the full list at the end. Fields can be accesses by a user specified field name, or by a zero-relative index. In the sample code we'll be sorting

the,9
quick,4
brown,7
fox,7
jumped,2
over,1
the,18
lazy,4
lazy,12
white,8
dog,9

so we'll need two fields - a VarChar (we'll name the field Word) and an Int (we'll call it Num)

rec.Fields.Append "Word",200,255       '200 = adVarChar             '
rec.Fields.Append "Num" ,2             '2   = adSmallInt            '

then to begin adding data we open the recordset

rec.Open

and to add a record we do

rec.AddNew
rec.Fields("Word") = "splunge"
rec.Fields("Num")  = 17

To sort the records you just tell the recordset what field or fields to use as

rec.Sort = "Word"
rec.Sort = "Num,Word"

You are not limited to just two sort fields. To step through the sorted data you move to the first record then process until EOF, doing a MoveNext to access each record in succession.

rec.MoveFirst

Do While Not rec.EOF
    wscript.Echo rec.Fields("Word"), vbTab, rec.Fields("Num")
    rec.MoveNext
Loop

When you are all done just

rec.Close

to close the recordset. You can sort on any, or as many columns as you want and you never have to write a line of sorting code.

Here are the values for the various field types

' ADODB.DataTypeEnum
Const adBigInt = 20
Const adBinary = 128
Const adBoolean = 11
Const adBSTR = 8
Const adChapter = 136
Const adChar = 129
Const adCurrency = 6
Const adDate = 7
Const adDBDate = 133
Const adDBFileTime = 137
Const adDBTime = 134
Const adDBTimeStamp = 135
Const adDecimal = 14
Const adDouble = 5
Const adEmpty = 0
Const adError = 10
Const adFileTime = 64
Const adGUID = 72
Const adIDispatch = 9
Const adInteger = 3
Const adIUnknown = 13
Const adLongVarBinary = 205
Const adLongVarChar = 201
Const adLongVarWChar = 203
Const adNumeric = 131
Const adPropVariant = 138
Const adSingle = 4
Const adSmallInt = 2
Const adTinyInt = 16
Const adUnsignedBigInt = 21
Const adUnsignedInt = 19
Const adUnsignedSmallInt = 18
Const adUnsignedTinyInt = 17
Const adUserDefined = 132
Const adVarBinary = 204
Const adVarChar = 200
Const adVariant = 12
Const adVarNumeric = 139
Const adVarWChar = 202
Const adWChar = 130