Sorting Output Records

A commonly used building block of SQL queries is the ORDER BY clause.
The ORDER BY clause can be used to specify that the output records should be sorted according to the values of selected fields.

In the following example, we are using the FS Input Format to retrieve a listing of the files in a specific directory, sorting the listing by the file size:

C:\>LogParser -i:FS -o:NAT "SELECT Path, Size FROM C:\MyDirectory\*.* ORDER BY Size"

Path                                    Size
--------------------------------------- ----
C:\MyDirectory\..                       0
C:\MyDirectory\.                        0
C:\MyDirectory\ieexec.exe.config        140
C:\MyDirectory\csc.exe.config           163
C:\MyDirectory\vbc.exe.config           163
C:\MyDirectory\jsc.exe.config           163
C:\MyDirectory\l_except.nlp             168
C:\MyDirectory\caspol.exe.config        353
C:\MyDirectory\ilasm.exe.config         353
C:\MyDirectory\ConfigWizards.exe.config 353

Tip: The ORDER BY clause must be the last clause appearing in a Log Parser SQL query.

By default, output records are sorted according to ascending values. We can change the sort direction by appending the DESC (for descending) or ASC (for ascending) keywords to the ORDER BY clause, as in the following example:

C:\>LogParser -i:FS -o:NAT "SELECT Path, Size FROM C:\MyDirectory\*.* ORDER BY Size DESC"

Path                                    Size
--------------------------------------- -------
C:\MyDirectory\mscorsvr.dll             2494464
C:\MyDirectory\mscorwks.dll             2482176
C:\MyDirectory\corperfmonsymbols.ini    2435148
C:\MyDirectory\mscorlib.dll             2088960
C:\MyDirectory\System.Windows.Forms.dll 2039808
C:\MyDirectory\System.Design.dll        1699840
C:\MyDirectory\mscorcfg.dll             1564672

Tip: Differently than the standard SQL language, the Log Parser SQL-Like language supports only one DESC or ASC keyword for the whole ORDER BY clause.

If we want our listing to be sorted first by file size and then by file creation time, we can do so by specifying both field-expressions in the ORDER BY clause:

C:\>LogParser -i:FS -o:NAT "SELECT Name, Size, CreationTime FROM C:\MyDirectory\*.* ORDER BY Size, CreationTime"

Name                     Size CreationTime
------------------------ ---- -----------------------
..                       0    2004-05-24 08:14:07.221
.                        0    2004-05-24 08:14:07.221
ieexec.exe.config        140  2004-05-24 08:14:21.441
csc.exe.config           163  2004-05-24 08:14:21.191
jsc.exe.config           163  2004-05-24 08:14:21.762
vbc.exe.config           163  2004-05-24 08:14:26.599
l_except.nlp             168  2004-05-24 08:14:21.812
caspol.exe.config        353  2004-05-24 08:14:20.920
ConfigWizards.exe.config 353  2004-05-24 08:14:21.21
cvtres.exe.config        353  2004-05-24 08:14:21.251

Since the sort operation is performed on output records, the Log Parser SQL-Like language requires that field-expressions appearing in the ORDER BY clause must also appear in the SELECT clause.
In other words, the set of field-expressions in the ORDER BY clause must be a subset of the field-expressions in the SELECT clause.
Thus, the following example is NOT correct:

SELECT SourceName, EventID FROM System
ORDER BY TimeGenerated

SELECT SourceName, EventID, TimeGenerated FROM System
ORDER BY TimeGenerated

© 2004 Microsoft Corporation. All rights reserved.