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

On the other hand, the following example IS correct:

SELECT SourceName, EventID, TimeGenerated FROM System
ORDER BY TimeGenerated