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 TimeGeneratedOn the other hand, the following example IS correct:
SELECT SourceName, EventID, TimeGenerated FROM System ORDER BY TimeGenerated