How to sort NULLs last with MSSQL

  • Normally SQL Server, like all other SQL Engines, sorts the NULLs at the beginning.
  • Sorting NULLs first is an SQL convention.
  • But, that's not was the users usually want.
  • The standard way of doing this, is to create a derived column, usually with a case statement and then do a sort on that derived column. The only problem is that we don't usually want to show the the derived column. So either we show the derived column or we create a temporary table with the derived column, then sort and run the select statement on the temporary table. This adds a lot of overhead.

Applies to:

  • Microsoft SQL Server 2000
  • Microsoft SQL Server 2005
  • Microsoft SQL Server 2008

Data used

  1. USE sql911;
  2. go
  3. SELECT CAST(RemoteLogName AS VARCHAR(16)) AS 'From',
  4.        CAST(Request AS VARCHAR(64)) AS 'Request',
  5.        StatusCode,BytesSent,
  6.        CAST(Referer AS VARCHAR(96)) AS 'Referer'
  7. FROM logs
  8. WHERE Cookie IS not null and remotehostname = 'sqlhacks.com'
  9. ORDER BY referer;
  10. go
From             Request                                                          StatusCode  BytesSent   Referer
---------------- ---------------------------------------------------------------- ----------- ----------- ------------------------------------------------------------------------------------------------
206.188.0.47     GET /index.php/Site/AllRecentChanges?action=rss HTTP/1.1         200         4115        NULL
72.34.230.108    GET / HTTP/1.0                                                   200         19843       NULL
206.188.0.12     GET /index.php/Site/AllRecentChanges?action=atom HTTP/1.1        200         51274       NULL
206.188.0.16     GET /index.php/Optimize/Index-Rebuild-Reorganize HTTP/1.1        200         10516       NULL
72.34.230.108    GET / HTTP/1.0                                                   200         19673       NULL
206.188.0.10     GET /index.php/Site/AllRecentChanges?action=rss HTTP/1.1         200         4050        NULL
206.188.0.10     GET / HTTP/1.1                                                   200         19996       NULL
206.188.0.10     GET /index.php/Site/AllRecentChanges?action=rss HTTP/1.1         200         4144        NULL
206.188.0.23     GET /index.php/Main/HomePage HTTP/1.1                            200         41288       NULL
206.188.0.16     GET /index.php/Site/AllRecentChanges?action=atom HTTP/1.1        200         52538       NULL
206.188.0.16     GET /index.php/Administration/Lightweight-diagnostics HTTP/1.1   200         15264       NULL
206.188.0.23     GET /index.php/Main/HomePage HTTP/1.1                            200         23449       NULL
142.166.3.123    GET /index.php/Optimize/Truncate-Delete HTTP/1.1                 200         10248       NULL
142.166.3.123    GET /index.php/Site/AllRecentChanges?action=rss HTTP/1.1         200         4018        NULL
139.131.241.10   GET /index.php/FAQs/Error_Messages HTTP/1.1                      200         18594       "http://www.google.com/search?q=%22Msg+208
206.211.127.2    GET /index.php/Summary/Multiple_Group_By HTTP/1.1                200         36068       "http://www.google.com/search?q=Column++is+invalid+in+the+select+list+because+it+is+not+containe
206.211.127.2    GET /pub/wsplus/csshover.htc HTTP/1.1                            200         3071        "http://www.google.com/search?q=Column++is+invalid+in+the+select+list+because+it+is+not+containe
205.200.74.135   GET /index.php/Administration/Stored-Procedures HTTP/1.1         200         240557      "http://www.google.com/search?q=sp%5Fsubscriptionsummary&hl=en&sourceid=gd&rls=GGLD
205.200.74.135   GET /pub/wsplus/csshover.htc HTTP/1.1                            200         3071        "http://www.google.com/search?q=sp%5Fsubscriptionsummary&hl=en&sourceid=gd&rls=GGLD
12.162.212.24    GET /index.php/Administration/Stored-Procedures HTTP/1.1         200         240557      "http://www.google.com/search?q=sp_check_constbytable_rowset&hl=en&rls=GGLG
12.162.212.24    GET /index.php/Administration/Stored-Procedures HTTP/1.1         200         240557      "http://www.google.com/search?q=sp_check_constbytable_rowset&hl=en&rls=GGLG
12.162.212.24    GET /pub/wsplus/csshover.htc HTTP/1.1                            200         3071        "http://www.google.com/search?q=sp_check_constbytable_rowset&hl=en&rls=GGLG
71.6.232.27      GET /index.php/Administration/Stored-Procedures HTTP/1.1         200         240557      "http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLD
71.6.232.27      GET /pub/wsplus/csshover.htc HTTP/1.1                            200         3071        "http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLD
71.6.232.27      GET /pub/wsplus/csshover.htc HTTP/1.1                            304         NULL        "http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLD
12.151.72.1      GET /index.php/Administration/Stored-Procedures HTTP/1.1         200         240557      "http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLG
12.151.72.1      GET /pub/wsplus/csshover.htc HTTP/1.1                            200         3071        "http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLG
12.151.72.1      GET /pub/wsplus/csshover.htc HTTP/1.1                            304         NULL        "http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLG
24.192.144.237   GET /index.php/Retrieve/Format_Numbers HTTP/1.1                  200         14915       "http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLJ
24.192.144.237   GET /pub/wsplus/csshover.htc HTTP/1.1                            200         3071        "http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLJ
24.192.144.237   GET /pub/wsplus/csshover.htc HTTP/1.1                            304         NULL        "http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLJ
212.242.32.164   GET /index.php/Retrieve/Changed-Row HTTP/1.1                     200         13474       "http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=SUNA
212.242.32.164   GET /pub/wsplus/csshover.htc HTTP/1.1                            200         3071        "http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=SUNA
64.28.26.178     GET /index.php/FAQs/Error_Messages HTTP/1.1                      200         18594       http://www.google.com/search?hl=en&client=safari&rls=en&q=Msg+50000&btnG=Search
64.28.26.178     GET /pub/wsplus/wsplus.css HTTP/1.1                              200         3393        http://www.sqlhacks.com/index.php/FAQs/Error_Messages
64.28.26.178     GET /pub/skins/fixflow/fontsizer.js HTTP/1.1                     200         4317        http://www.sqlhacks.com/index.php/FAQs/Error_Messages
64.28.26.178     GET /pub/skins/favicon.ico HTTP/1.1                              200         633         http://www.sqlhacks.com/index.php/FAQs/Error_Messages
64.28.26.178     GET /pub/skins/fixflow/css/layout-main.css HTTP/1.1              200         6192        http://www.sqlhacks.com/index.php/FAQs/Error_Messages
64.28.26.178     GET /pub/skins/fixflow/css/layout-fixflow.css HTTP/1.1           200         2001        http://www.sqlhacks.com/index.php/FAQs/Error_Messages
64.28.26.178     GET /pub/skins/fixflow/css/layout-print.css HTTP/1.1             200         981         http://www.sqlhacks.com/index.php/FAQs/Error_Messages
64.28.26.178     GET /pub/skins/fixflow/css/rightbar.css HTTP/1.1                 200         787         http://www.sqlhacks.com/index.php/FAQs/Error_Messages
64.28.26.178     GET /pub/skins/fixflow/css/font-verdana.css HTTP/1.1             200         2752        http://www.sqlhacks.com/index.php/FAQs/Error_Messages
64.28.26.178     GET /uploads/PmWiki/button-rss.png HTTP/1.1                      200         280         http://www.sqlhacks.com/index.php/FAQs/Error_Messages
64.28.26.178     GET /pub/skins/sqlhack-04.gif HTTP/1.1                           200         618         http://www.sqlhacks.com/index.php/FAQs/Error_Messages
64.28.26.178     GET /pub/skins/fixflow/css/c-silver.css HTTP/1.1                 200         3771        http://www.sqlhacks.com/index.php/FAQs/Error_Messages
64.28.26.178     GET /pub/skins/fixflow/images/silver100-top.jpg HTTP/1.1         200         376         http://www.sqlhacks.com/index.php/FAQs/Error_Messages
64.28.26.178     GET /pub/skins/fixflow/images/bullet7.gif HTTP/1.1               200         845         http://www.sqlhacks.com/index.php/FAQs/Error_Messages
64.28.26.178     GET /pub/wsplus/important.gif HTTP/1.1                           200         1258        http://www.sqlhacks.com/index.php/FAQs/Error_Messages
64.28.26.178     GET /pub/wsplus/tip.gif HTTP/1.1                                 200         1276        http://www.sqlhacks.com/index.php/FAQs/Error_Messages
64.28.26.178     GET /pub/skins/fixflow/css/pm-core.css HTTP/1.1                  200         2112        http://www.sqlhacks.com/index.php/FAQs/Error_Messages
59.93.7.244      GET /pub/wsplus/wsplus.css HTTP/1.1                              200         3393        http://www.sqlhacks.com/index.php/Summary/Multiple_Group_By
59.93.7.244      GET /pub/skins/fixflow/css/rightbar.css HTTP/1.1                 200         787         http://www.sqlhacks.com/index.php/Summary/Multiple_Group_By

(52 row(s) affected)

The 'standard SQL' sorts the NULLs first.

Almost right way of sorting NULLs last

  1. SELECT CAST(RemoteLogName AS VARCHAR(16)) AS 'From',
  2.        CAST(Request AS VARCHAR(64)) AS 'Request',
  3.        StatusCode,BytesSent,
  4.        CAST(Referer AS VARCHAR(96)) AS 'Referer',
  5.        CASE WHEN referer IS null
  6.             THEN 1
  7.             ELSE 0
  8.        END AS 'Sorting column'
  9. FROM logs
  10. WHERE Cookie IS not null and remotehostname = 'sqlhacks.com'
  11. ORDER BY 'Sorting column',
  12.          referer;
  13. go
From             Request                                                          StatusCode  BytesSent   Referer                                                                                          Sorting column
---------------- ---------------------------------------------------------------- ----------- ----------- ------------------------------------------------------------------------------------------------ --------------
139.131.241.10   GET /index.php/FAQs/Error_Messages HTTP/1.1                      200         18594       "http://www.google.com/search?q=%22Msg+208                                                       0
206.211.127.2    GET /index.php/Summary/Multiple_Group_By HTTP/1.1                200         36068       "http://www.google.com/search?q=Column++is+invalid+in+the+select+list+because+it+is+not+containe 0
206.211.127.2    GET /pub/wsplus/csshover.htc HTTP/1.1                            200         3071        "http://www.google.com/search?q=Column++is+invalid+in+the+select+list+because+it+is+not+containe 0
205.200.74.135   GET /index.php/Administration/Stored-Procedures HTTP/1.1         200         240557      "http://www.google.com/search?q=sp%5Fsubscriptionsummary&hl=en&sourceid=gd&rls=GGLD              0
205.200.74.135   GET /pub/wsplus/csshover.htc HTTP/1.1                            200         3071        "http://www.google.com/search?q=sp%5Fsubscriptionsummary&hl=en&sourceid=gd&rls=GGLD              0
12.162.212.24    GET /index.php/Administration/Stored-Procedures HTTP/1.1         200         240557      "http://www.google.com/search?q=sp_check_constbytable_rowset&hl=en&rls=GGLG                      0
12.162.212.24    GET /index.php/Administration/Stored-Procedures HTTP/1.1         200         240557      "http://www.google.com/search?q=sp_check_constbytable_rowset&hl=en&rls=GGLG                      0
12.162.212.24    GET /pub/wsplus/csshover.htc HTTP/1.1                            200         3071        "http://www.google.com/search?q=sp_check_constbytable_rowset&hl=en&rls=GGLG                      0
71.6.232.27      GET /index.php/Administration/Stored-Procedures HTTP/1.1         200         240557      "http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLD                               0
71.6.232.27      GET /pub/wsplus/csshover.htc HTTP/1.1                            200         3071        "http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLD                               0
71.6.232.27      GET /pub/wsplus/csshover.htc HTTP/1.1                            304         NULL        "http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLD                               0
12.151.72.1      GET /index.php/Administration/Stored-Procedures HTTP/1.1         200         240557      "http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLG                               0
12.151.72.1      GET /pub/wsplus/csshover.htc HTTP/1.1                            200         3071        "http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLG                               0
12.151.72.1      GET /pub/wsplus/csshover.htc HTTP/1.1                            304         NULL        "http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLG                               0
24.192.144.237   GET /index.php/Retrieve/Format_Numbers HTTP/1.1                  200         14915       "http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLJ                               0
24.192.144.237   GET /pub/wsplus/csshover.htc HTTP/1.1                            200         3071        "http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLJ                               0
24.192.144.237   GET /pub/wsplus/csshover.htc HTTP/1.1                            304         NULL        "http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLJ                               0
212.242.32.164   GET /index.php/Retrieve/Changed-Row HTTP/1.1                     200         13474       "http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=SUNA                               0
212.242.32.164   GET /pub/wsplus/csshover.htc HTTP/1.1                            200         3071        "http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=SUNA                               0
64.28.26.178     GET /index.php/FAQs/Error_Messages HTTP/1.1                      200         18594       http://www.google.com/search?hl=en&client=safari&rls=en&q=Msg+50000&btnG=Search                  0
64.28.26.178     GET /pub/wsplus/wsplus.css HTTP/1.1                              200         3393        http://www.sqlhacks.com/index.php/FAQs/Error_Messages                                            0
64.28.26.178     GET /pub/skins/fixflow/fontsizer.js HTTP/1.1                     200         4317        http://www.sqlhacks.com/index.php/FAQs/Error_Messages                                            0
64.28.26.178     GET /pub/skins/favicon.ico HTTP/1.1                              200         633         http://www.sqlhacks.com/index.php/FAQs/Error_Messages                                            0
64.28.26.178     GET /pub/skins/fixflow/css/layout-main.css HTTP/1.1              200         6192        http://www.sqlhacks.com/index.php/FAQs/Error_Messages                                            0
64.28.26.178     GET /pub/skins/fixflow/css/layout-fixflow.css HTTP/1.1           200         2001        http://www.sqlhacks.com/index.php/FAQs/Error_Messages                                            0
64.28.26.178     GET /pub/skins/fixflow/css/layout-print.css HTTP/1.1             200         981         http://www.sqlhacks.com/index.php/FAQs/Error_Messages                                            0
64.28.26.178     GET /pub/skins/fixflow/css/rightbar.css HTTP/1.1                 200         787         http://www.sqlhacks.com/index.php/FAQs/Error_Messages                                            0
64.28.26.178     GET /pub/skins/fixflow/css/font-verdana.css HTTP/1.1             200         2752        http://www.sqlhacks.com/index.php/FAQs/Error_Messages                                            0
64.28.26.178     GET /uploads/PmWiki/button-rss.png HTTP/1.1                      200         280         http://www.sqlhacks.com/index.php/FAQs/Error_Messages                                            0
64.28.26.178     GET /pub/skins/sqlhack-04.gif HTTP/1.1                           200         618         http://www.sqlhacks.com/index.php/FAQs/Error_Messages                                            0
64.28.26.178     GET /pub/skins/fixflow/css/c-silver.css HTTP/1.1                 200         3771        http://www.sqlhacks.com/index.php/FAQs/Error_Messages                                            0
64.28.26.178     GET /pub/skins/fixflow/images/silver100-top.jpg HTTP/1.1         200         376         http://www.sqlhacks.com/index.php/FAQs/Error_Messages                                            0
64.28.26.178     GET /pub/skins/fixflow/images/bullet7.gif HTTP/1.1               200         845         http://www.sqlhacks.com/index.php/FAQs/Error_Messages                                            0
64.28.26.178     GET /pub/wsplus/important.gif HTTP/1.1                           200         1258        http://www.sqlhacks.com/index.php/FAQs/Error_Messages                                            0
64.28.26.178     GET /pub/wsplus/tip.gif HTTP/1.1                                 200         1276        http://www.sqlhacks.com/index.php/FAQs/Error_Messages                                            0
64.28.26.178     GET /pub/skins/fixflow/css/pm-core.css HTTP/1.1                  200         2112        http://www.sqlhacks.com/index.php/FAQs/Error_Messages                                            0
59.93.7.244      GET /pub/wsplus/wsplus.css HTTP/1.1                              200         3393        http://www.sqlhacks.com/index.php/Summary/Multiple_Group_By                                      0
59.93.7.244      GET /pub/skins/fixflow/css/rightbar.css HTTP/1.1                 200         787         http://www.sqlhacks.com/index.php/Summary/Multiple_Group_By                                      0
142.166.3.123    GET /index.php/Site/AllRecentChanges?action=rss HTTP/1.1         200         4018        NULL                                                                                             1
206.188.0.23     GET /index.php/Main/HomePage HTTP/1.1                            200         23449       NULL                                                                                             1
142.166.3.123    GET /index.php/Optimize/Truncate-Delete HTTP/1.1                 200         10248       NULL                                                                                             1
206.188.0.16     GET /index.php/Site/AllRecentChanges?action=atom HTTP/1.1        200         52538       NULL                                                                                             1
206.188.0.16     GET /index.php/Administration/Lightweight-diagnostics HTTP/1.1   200         15264       NULL                                                                                             1
206.188.0.47     GET /index.php/Site/AllRecentChanges?action=rss HTTP/1.1         200         4115        NULL                                                                                             1
72.34.230.108    GET / HTTP/1.0                                                   200         19843       NULL                                                                                             1
206.188.0.12     GET /index.php/Site/AllRecentChanges?action=atom HTTP/1.1        200         51274       NULL                                                                                             1
206.188.0.16     GET /index.php/Optimize/Index-Rebuild-Reorganize HTTP/1.1        200         10516       NULL                                                                                             1
72.34.230.108    GET / HTTP/1.0                                                   200         19673       NULL                                                                                             1
206.188.0.10     GET /index.php/Site/AllRecentChanges?action=rss HTTP/1.1         200         4050        NULL                                                                                             1
206.188.0.10     GET / HTTP/1.1                                                   200         19996       NULL                                                                                             1
206.188.0.10     GET /index.php/Site/AllRecentChanges?action=rss HTTP/1.1         200         4144        NULL                                                                                             1
206.188.0.23     GET /index.php/Main/HomePage HTTP/1.1                            200         41288       NULL                                                                                             1

(52 row(s) affected)
  1. The sort is correct, but I didn't want to display the derived column, so I placed it last hoping people won't notice it.
  2. Either I display the extra column or I save the data to a temp table, and display the temp table.

Right way of sorting NULLs last

  1. SELECT CAST(RemoteLogName AS VARCHAR(16)) AS 'From',
  2.        CAST(Request AS VARCHAR(64)) AS 'Request',
  3.        StatusCode,BytesSent,
  4.        CAST(Referer AS VARCHAR(96)) AS 'Referer'
  5. FROM logs
  6. WHERE Cookie IS not null and remotehostname = 'sqlhacks.com'
  7. ORDER BY CASE WHEN referer IS null
  8.               THEN 1
  9.               ELSE 0
  10.          END,
  11.          referer;
  12. go
From             Request                                                          StatusCode  BytesSent   Referer
---------------- ---------------------------------------------------------------- ----------- ----------- ------------------------------------------------------------------------------------------------
139.131.241.10   GET /index.php/FAQs/Error_Messages HTTP/1.1                      200         18594       "http://www.google.com/search?q=%22Msg+208
206.211.127.2    GET /index.php/Summary/Multiple_Group_By HTTP/1.1                200         36068       "http://www.google.com/search?q=Column++is+invalid+in+the+select+list+because+it+is+not+containe
206.211.127.2    GET /pub/wsplus/csshover.htc HTTP/1.1                            200         3071        "http://www.google.com/search?q=Column++is+invalid+in+the+select+list+because+it+is+not+containe
205.200.74.135   GET /index.php/Administration/Stored-Procedures HTTP/1.1         200         240557      "http://www.google.com/search?q=sp%5Fsubscriptionsummary&hl=en&sourceid=gd&rls=GGLD
205.200.74.135   GET /pub/wsplus/csshover.htc HTTP/1.1                            200         3071        "http://www.google.com/search?q=sp%5Fsubscriptionsummary&hl=en&sourceid=gd&rls=GGLD
12.162.212.24    GET /index.php/Administration/Stored-Procedures HTTP/1.1         200         240557      "http://www.google.com/search?q=sp_check_constbytable_rowset&hl=en&rls=GGLG
12.162.212.24    GET /index.php/Administration/Stored-Procedures HTTP/1.1         200         240557      "http://www.google.com/search?q=sp_check_constbytable_rowset&hl=en&rls=GGLG
12.162.212.24    GET /pub/wsplus/csshover.htc HTTP/1.1                            200         3071        "http://www.google.com/search?q=sp_check_constbytable_rowset&hl=en&rls=GGLG
71.6.232.27      GET /index.php/Administration/Stored-Procedures HTTP/1.1         200         240557      "http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLD
71.6.232.27      GET /pub/wsplus/csshover.htc HTTP/1.1                            200         3071        "http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLD
71.6.232.27      GET /pub/wsplus/csshover.htc HTTP/1.1                            304         NULL        "http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLD
12.151.72.1      GET /index.php/Administration/Stored-Procedures HTTP/1.1         200         240557      "http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLG
12.151.72.1      GET /pub/wsplus/csshover.htc HTTP/1.1                            200         3071        "http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLG
12.151.72.1      GET /pub/wsplus/csshover.htc HTTP/1.1                            304         NULL        "http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLG
24.192.144.237   GET /index.php/Retrieve/Format_Numbers HTTP/1.1                  200         14915       "http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLJ
24.192.144.237   GET /pub/wsplus/csshover.htc HTTP/1.1                            200         3071        "http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLJ
24.192.144.237   GET /pub/wsplus/csshover.htc HTTP/1.1                            304         NULL        "http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLJ
212.242.32.164   GET /index.php/Retrieve/Changed-Row HTTP/1.1                     200         13474       "http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=SUNA
212.242.32.164   GET /pub/wsplus/csshover.htc HTTP/1.1                            200         3071        "http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=SUNA
64.28.26.178     GET /index.php/FAQs/Error_Messages HTTP/1.1                      200         18594       http://www.google.com/search?hl=en&client=safari&rls=en&q=Msg+50000&btnG=Search
64.28.26.178     GET /pub/wsplus/wsplus.css HTTP/1.1                              200         3393        http://www.sqlhacks.com/index.php/FAQs/Error_Messages
64.28.26.178     GET /pub/skins/fixflow/fontsizer.js HTTP/1.1                     200         4317        http://www.sqlhacks.com/index.php/FAQs/Error_Messages
64.28.26.178     GET /pub/skins/favicon.ico HTTP/1.1                              200         633         http://www.sqlhacks.com/index.php/FAQs/Error_Messages
64.28.26.178     GET /pub/skins/fixflow/css/layout-main.css HTTP/1.1              200         6192        http://www.sqlhacks.com/index.php/FAQs/Error_Messages
64.28.26.178     GET /pub/skins/fixflow/css/layout-fixflow.css HTTP/1.1           200         2001        http://www.sqlhacks.com/index.php/FAQs/Error_Messages
64.28.26.178     GET /pub/skins/fixflow/css/layout-print.css HTTP/1.1             200         981         http://www.sqlhacks.com/index.php/FAQs/Error_Messages
64.28.26.178     GET /pub/skins/fixflow/css/rightbar.css HTTP/1.1                 200         787         http://www.sqlhacks.com/index.php/FAQs/Error_Messages
64.28.26.178     GET /pub/skins/fixflow/css/font-verdana.css HTTP/1.1             200         2752        http://www.sqlhacks.com/index.php/FAQs/Error_Messages
64.28.26.178     GET /uploads/PmWiki/button-rss.png HTTP/1.1                      200         280         http://www.sqlhacks.com/index.php/FAQs/Error_Messages
64.28.26.178     GET /pub/skins/sqlhack-04.gif HTTP/1.1                           200         618         http://www.sqlhacks.com/index.php/FAQs/Error_Messages
64.28.26.178     GET /pub/skins/fixflow/css/c-silver.css HTTP/1.1                 200         3771        http://www.sqlhacks.com/index.php/FAQs/Error_Messages
64.28.26.178     GET /pub/skins/fixflow/images/silver100-top.jpg HTTP/1.1         200         376         http://www.sqlhacks.com/index.php/FAQs/Error_Messages
64.28.26.178     GET /pub/skins/fixflow/images/bullet7.gif HTTP/1.1               200         845         http://www.sqlhacks.com/index.php/FAQs/Error_Messages
64.28.26.178     GET /pub/wsplus/important.gif HTTP/1.1                           200         1258        http://www.sqlhacks.com/index.php/FAQs/Error_Messages
64.28.26.178     GET /pub/wsplus/tip.gif HTTP/1.1                                 200         1276        http://www.sqlhacks.com/index.php/FAQs/Error_Messages
64.28.26.178     GET /pub/skins/fixflow/css/pm-core.css HTTP/1.1                  200         2112        http://www.sqlhacks.com/index.php/FAQs/Error_Messages
59.93.7.244      GET /pub/wsplus/wsplus.css HTTP/1.1                              200         3393        http://www.sqlhacks.com/index.php/Summary/Multiple_Group_By
59.93.7.244      GET /pub/skins/fixflow/css/rightbar.css HTTP/1.1                 200         787         http://www.sqlhacks.com/index.php/Summary/Multiple_Group_By
142.166.3.123    GET /index.php/Site/AllRecentChanges?action=rss HTTP/1.1         200         4018        NULL
206.188.0.23     GET /index.php/Main/HomePage HTTP/1.1                            200         23449       NULL
142.166.3.123    GET /index.php/Optimize/Truncate-Delete HTTP/1.1                 200         10248       NULL
206.188.0.16     GET /index.php/Site/AllRecentChanges?action=atom HTTP/1.1        200         52538       NULL
206.188.0.16     GET /index.php/Administration/Lightweight-diagnostics HTTP/1.1   200         15264       NULL
206.188.0.47     GET /index.php/Site/AllRecentChanges?action=rss HTTP/1.1         200         4115        NULL
72.34.230.108    GET / HTTP/1.0                                                   200         19843       NULL
206.188.0.12     GET /index.php/Site/AllRecentChanges?action=atom HTTP/1.1        200         51274       NULL
206.188.0.16     GET /index.php/Optimize/Index-Rebuild-Reorganize HTTP/1.1        200         10516       NULL
72.34.230.108    GET / HTTP/1.0                                                   200         19673       NULL
206.188.0.10     GET /index.php/Site/AllRecentChanges?action=rss HTTP/1.1         200         4050        NULL
206.188.0.10     GET / HTTP/1.1                                                   200         19996       NULL
206.188.0.10     GET /index.php/Site/AllRecentChanges?action=rss HTTP/1.1         200         4144        NULL
206.188.0.23     GET /index.php/Main/HomePage HTTP/1.1                            200         41288       NULL

(52 row(s) affected)
  • Line 7 to 10: order by case when referer is null then 1 else 0 end. Doing the case in the order by section allows for the proper sorting without adding a new column.