How to do NULLS LAST in SQLite?





.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}







21















I'd like to sort my result with all NULL columns last (NULLS LAST), as specified in the SQL:2003 extension T611. Sadly, SQLite seems to not support it. Is there a clever workaround?










share|improve this question































    21















    I'd like to sort my result with all NULL columns last (NULLS LAST), as specified in the SQL:2003 extension T611. Sadly, SQLite seems to not support it. Is there a clever workaround?










    share|improve this question



























      21












      21








      21


      3






      I'd like to sort my result with all NULL columns last (NULLS LAST), as specified in the SQL:2003 extension T611. Sadly, SQLite seems to not support it. Is there a clever workaround?










      share|improve this question
















      I'd like to sort my result with all NULL columns last (NULLS LAST), as specified in the SQL:2003 extension T611. Sadly, SQLite seems to not support it. Is there a clever workaround?







      sql sqlite sqlite3 null






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited Sep 19 '12 at 21:27









      Mat

      168k29323349




      168k29323349










      asked Sep 19 '12 at 21:23









      Ortwin GentzOrtwin Gentz

      32.7k20114189




      32.7k20114189
























          5 Answers
          5






          active

          oldest

          votes


















          21














          could this work?



          SELECT ....... ORDER BY COALESCE(col1,col2,col3,etc) IS NULL


          I am kind of confused by your wording "all NULL columns last". If you want all NULL values last in a particular column, use this:



          SELECT ....... ORDER BY col1 IS NULL





          share|improve this answer


























          • Don't you mean IS NOT NULL?

            – dan04
            Sep 19 '12 at 21:28











          • @dan04 thx, you're right

            – mvds
            Sep 19 '12 at 21:30






          • 3





            I think it should be IS NULL to get the nulls last :)

            – Blorgbeard
            Sep 19 '12 at 21:30











          • @Blorgbeard crap...

            – mvds
            Sep 19 '12 at 21:31











          • Hmm I read the question as "all columns NULL", not sure if that is even what was asked ;-)

            – mvds
            Sep 19 '12 at 21:32



















          19














          While I somewhat like Blorgbeard's answer, this variant doesn't care about supplying a valid 'fake' value of the right datatype.



          ORDER BY CASE WHEN SOMECOL IS NULL THEN 1 ELSE 0 END, SOMECOL


          Alternatively, even if you wanted to use a fake value, I would prefer IFNULL!



          ORDER BY IFNULL(SOMECOL,-9999)


          As Michael noted, SQLite uses IFNULL. You can use the ANSI-SQL universal version COALESCE as well.






          share|improve this answer


























          • Minor correction I think ... in SQLite, ISNULL should be IFNULL.

            – Michael
            Jan 1 '17 at 20:03











          • Thanks @Michael, updated with better info

            – RichardTheKiwi
            Jan 3 '17 at 2:49



















          4














          You can do something like this to fake it:



          select * from test
          order by case ordercol when null then 1 else 0 end, ordercol





          share|improve this answer


























          • why not order by case when ordercol is null then 0 else 1, ordercol ? saves a weird bug when someone puts values below -100 in ordercol.

            – mvds
            Sep 19 '12 at 21:36











          • That's a good point. I just wrote a vague example off the top of my head because we don't know what the data looks like. But your correction works better in general - applied it!

            – Blorgbeard
            Sep 19 '12 at 21:38



















          2














          I ran into the same problem.
          I found out this could work:



          (I didn't find any isnull function for SQLite)



          order by ifnull(column_what_you_want_to_sort,'value in case of null')





          share|improve this answer

































            0














            My solution was to reverse the range of my priority column and use order by desc (sort in descending order). This puts nulls at the end in one go.



            Clearly this only works if you control your priority ordering column, but it's pretty great if you can swing it!



            If that's doesn't work, I found a union query worked. Simply union two queries and add the constraint where not null on the first and where null on the second. The second query will be at the end of the first.






            share|improve this answer
























              Your Answer






              StackExchange.ifUsing("editor", function () {
              StackExchange.using("externalEditor", function () {
              StackExchange.using("snippets", function () {
              StackExchange.snippets.init();
              });
              });
              }, "code-snippets");

              StackExchange.ready(function() {
              var channelOptions = {
              tags: "".split(" "),
              id: "1"
              };
              initTagRenderer("".split(" "), "".split(" "), channelOptions);

              StackExchange.using("externalEditor", function() {
              // Have to fire editor after snippets, if snippets enabled
              if (StackExchange.settings.snippets.snippetsEnabled) {
              StackExchange.using("snippets", function() {
              createEditor();
              });
              }
              else {
              createEditor();
              }
              });

              function createEditor() {
              StackExchange.prepareEditor({
              heartbeatType: 'answer',
              autoActivateHeartbeat: false,
              convertImagesToLinks: true,
              noModals: true,
              showLowRepImageUploadWarning: true,
              reputationToPostImages: 10,
              bindNavPrevention: true,
              postfix: "",
              imageUploader: {
              brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
              contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
              allowUrls: true
              },
              onDemand: true,
              discardSelector: ".discard-answer"
              ,immediatelyShowMarkdownHelp:true
              });


              }
              });














              draft saved

              draft discarded


















              StackExchange.ready(
              function () {
              StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f12503120%2fhow-to-do-nulls-last-in-sqlite%23new-answer', 'question_page');
              }
              );

              Post as a guest















              Required, but never shown

























              5 Answers
              5






              active

              oldest

              votes








              5 Answers
              5






              active

              oldest

              votes









              active

              oldest

              votes






              active

              oldest

              votes









              21














              could this work?



              SELECT ....... ORDER BY COALESCE(col1,col2,col3,etc) IS NULL


              I am kind of confused by your wording "all NULL columns last". If you want all NULL values last in a particular column, use this:



              SELECT ....... ORDER BY col1 IS NULL





              share|improve this answer


























              • Don't you mean IS NOT NULL?

                – dan04
                Sep 19 '12 at 21:28











              • @dan04 thx, you're right

                – mvds
                Sep 19 '12 at 21:30






              • 3





                I think it should be IS NULL to get the nulls last :)

                – Blorgbeard
                Sep 19 '12 at 21:30











              • @Blorgbeard crap...

                – mvds
                Sep 19 '12 at 21:31











              • Hmm I read the question as "all columns NULL", not sure if that is even what was asked ;-)

                – mvds
                Sep 19 '12 at 21:32
















              21














              could this work?



              SELECT ....... ORDER BY COALESCE(col1,col2,col3,etc) IS NULL


              I am kind of confused by your wording "all NULL columns last". If you want all NULL values last in a particular column, use this:



              SELECT ....... ORDER BY col1 IS NULL





              share|improve this answer


























              • Don't you mean IS NOT NULL?

                – dan04
                Sep 19 '12 at 21:28











              • @dan04 thx, you're right

                – mvds
                Sep 19 '12 at 21:30






              • 3





                I think it should be IS NULL to get the nulls last :)

                – Blorgbeard
                Sep 19 '12 at 21:30











              • @Blorgbeard crap...

                – mvds
                Sep 19 '12 at 21:31











              • Hmm I read the question as "all columns NULL", not sure if that is even what was asked ;-)

                – mvds
                Sep 19 '12 at 21:32














              21












              21








              21







              could this work?



              SELECT ....... ORDER BY COALESCE(col1,col2,col3,etc) IS NULL


              I am kind of confused by your wording "all NULL columns last". If you want all NULL values last in a particular column, use this:



              SELECT ....... ORDER BY col1 IS NULL





              share|improve this answer















              could this work?



              SELECT ....... ORDER BY COALESCE(col1,col2,col3,etc) IS NULL


              I am kind of confused by your wording "all NULL columns last". If you want all NULL values last in a particular column, use this:



              SELECT ....... ORDER BY col1 IS NULL






              share|improve this answer














              share|improve this answer



              share|improve this answer








              edited Sep 19 '12 at 21:34

























              answered Sep 19 '12 at 21:26









              mvdsmvds

              39.3k686103




              39.3k686103













              • Don't you mean IS NOT NULL?

                – dan04
                Sep 19 '12 at 21:28











              • @dan04 thx, you're right

                – mvds
                Sep 19 '12 at 21:30






              • 3





                I think it should be IS NULL to get the nulls last :)

                – Blorgbeard
                Sep 19 '12 at 21:30











              • @Blorgbeard crap...

                – mvds
                Sep 19 '12 at 21:31











              • Hmm I read the question as "all columns NULL", not sure if that is even what was asked ;-)

                – mvds
                Sep 19 '12 at 21:32



















              • Don't you mean IS NOT NULL?

                – dan04
                Sep 19 '12 at 21:28











              • @dan04 thx, you're right

                – mvds
                Sep 19 '12 at 21:30






              • 3





                I think it should be IS NULL to get the nulls last :)

                – Blorgbeard
                Sep 19 '12 at 21:30











              • @Blorgbeard crap...

                – mvds
                Sep 19 '12 at 21:31











              • Hmm I read the question as "all columns NULL", not sure if that is even what was asked ;-)

                – mvds
                Sep 19 '12 at 21:32

















              Don't you mean IS NOT NULL?

              – dan04
              Sep 19 '12 at 21:28





              Don't you mean IS NOT NULL?

              – dan04
              Sep 19 '12 at 21:28













              @dan04 thx, you're right

              – mvds
              Sep 19 '12 at 21:30





              @dan04 thx, you're right

              – mvds
              Sep 19 '12 at 21:30




              3




              3





              I think it should be IS NULL to get the nulls last :)

              – Blorgbeard
              Sep 19 '12 at 21:30





              I think it should be IS NULL to get the nulls last :)

              – Blorgbeard
              Sep 19 '12 at 21:30













              @Blorgbeard crap...

              – mvds
              Sep 19 '12 at 21:31





              @Blorgbeard crap...

              – mvds
              Sep 19 '12 at 21:31













              Hmm I read the question as "all columns NULL", not sure if that is even what was asked ;-)

              – mvds
              Sep 19 '12 at 21:32





              Hmm I read the question as "all columns NULL", not sure if that is even what was asked ;-)

              – mvds
              Sep 19 '12 at 21:32













              19














              While I somewhat like Blorgbeard's answer, this variant doesn't care about supplying a valid 'fake' value of the right datatype.



              ORDER BY CASE WHEN SOMECOL IS NULL THEN 1 ELSE 0 END, SOMECOL


              Alternatively, even if you wanted to use a fake value, I would prefer IFNULL!



              ORDER BY IFNULL(SOMECOL,-9999)


              As Michael noted, SQLite uses IFNULL. You can use the ANSI-SQL universal version COALESCE as well.






              share|improve this answer


























              • Minor correction I think ... in SQLite, ISNULL should be IFNULL.

                – Michael
                Jan 1 '17 at 20:03











              • Thanks @Michael, updated with better info

                – RichardTheKiwi
                Jan 3 '17 at 2:49
















              19














              While I somewhat like Blorgbeard's answer, this variant doesn't care about supplying a valid 'fake' value of the right datatype.



              ORDER BY CASE WHEN SOMECOL IS NULL THEN 1 ELSE 0 END, SOMECOL


              Alternatively, even if you wanted to use a fake value, I would prefer IFNULL!



              ORDER BY IFNULL(SOMECOL,-9999)


              As Michael noted, SQLite uses IFNULL. You can use the ANSI-SQL universal version COALESCE as well.






              share|improve this answer


























              • Minor correction I think ... in SQLite, ISNULL should be IFNULL.

                – Michael
                Jan 1 '17 at 20:03











              • Thanks @Michael, updated with better info

                – RichardTheKiwi
                Jan 3 '17 at 2:49














              19












              19








              19







              While I somewhat like Blorgbeard's answer, this variant doesn't care about supplying a valid 'fake' value of the right datatype.



              ORDER BY CASE WHEN SOMECOL IS NULL THEN 1 ELSE 0 END, SOMECOL


              Alternatively, even if you wanted to use a fake value, I would prefer IFNULL!



              ORDER BY IFNULL(SOMECOL,-9999)


              As Michael noted, SQLite uses IFNULL. You can use the ANSI-SQL universal version COALESCE as well.






              share|improve this answer















              While I somewhat like Blorgbeard's answer, this variant doesn't care about supplying a valid 'fake' value of the right datatype.



              ORDER BY CASE WHEN SOMECOL IS NULL THEN 1 ELSE 0 END, SOMECOL


              Alternatively, even if you wanted to use a fake value, I would prefer IFNULL!



              ORDER BY IFNULL(SOMECOL,-9999)


              As Michael noted, SQLite uses IFNULL. You can use the ANSI-SQL universal version COALESCE as well.







              share|improve this answer














              share|improve this answer



              share|improve this answer








              edited Jan 4 at 12:12









              TWiStErRob

              28.2k8106190




              28.2k8106190










              answered Sep 19 '12 at 21:37









              RichardTheKiwiRichardTheKiwi

              87.9k22158231




              87.9k22158231













              • Minor correction I think ... in SQLite, ISNULL should be IFNULL.

                – Michael
                Jan 1 '17 at 20:03











              • Thanks @Michael, updated with better info

                – RichardTheKiwi
                Jan 3 '17 at 2:49



















              • Minor correction I think ... in SQLite, ISNULL should be IFNULL.

                – Michael
                Jan 1 '17 at 20:03











              • Thanks @Michael, updated with better info

                – RichardTheKiwi
                Jan 3 '17 at 2:49

















              Minor correction I think ... in SQLite, ISNULL should be IFNULL.

              – Michael
              Jan 1 '17 at 20:03





              Minor correction I think ... in SQLite, ISNULL should be IFNULL.

              – Michael
              Jan 1 '17 at 20:03













              Thanks @Michael, updated with better info

              – RichardTheKiwi
              Jan 3 '17 at 2:49





              Thanks @Michael, updated with better info

              – RichardTheKiwi
              Jan 3 '17 at 2:49











              4














              You can do something like this to fake it:



              select * from test
              order by case ordercol when null then 1 else 0 end, ordercol





              share|improve this answer


























              • why not order by case when ordercol is null then 0 else 1, ordercol ? saves a weird bug when someone puts values below -100 in ordercol.

                – mvds
                Sep 19 '12 at 21:36











              • That's a good point. I just wrote a vague example off the top of my head because we don't know what the data looks like. But your correction works better in general - applied it!

                – Blorgbeard
                Sep 19 '12 at 21:38
















              4














              You can do something like this to fake it:



              select * from test
              order by case ordercol when null then 1 else 0 end, ordercol





              share|improve this answer


























              • why not order by case when ordercol is null then 0 else 1, ordercol ? saves a weird bug when someone puts values below -100 in ordercol.

                – mvds
                Sep 19 '12 at 21:36











              • That's a good point. I just wrote a vague example off the top of my head because we don't know what the data looks like. But your correction works better in general - applied it!

                – Blorgbeard
                Sep 19 '12 at 21:38














              4












              4








              4







              You can do something like this to fake it:



              select * from test
              order by case ordercol when null then 1 else 0 end, ordercol





              share|improve this answer















              You can do something like this to fake it:



              select * from test
              order by case ordercol when null then 1 else 0 end, ordercol






              share|improve this answer














              share|improve this answer



              share|improve this answer








              edited Sep 19 '12 at 21:37

























              answered Sep 19 '12 at 21:29









              BlorgbeardBlorgbeard

              76.3k40197249




              76.3k40197249













              • why not order by case when ordercol is null then 0 else 1, ordercol ? saves a weird bug when someone puts values below -100 in ordercol.

                – mvds
                Sep 19 '12 at 21:36











              • That's a good point. I just wrote a vague example off the top of my head because we don't know what the data looks like. But your correction works better in general - applied it!

                – Blorgbeard
                Sep 19 '12 at 21:38



















              • why not order by case when ordercol is null then 0 else 1, ordercol ? saves a weird bug when someone puts values below -100 in ordercol.

                – mvds
                Sep 19 '12 at 21:36











              • That's a good point. I just wrote a vague example off the top of my head because we don't know what the data looks like. But your correction works better in general - applied it!

                – Blorgbeard
                Sep 19 '12 at 21:38

















              why not order by case when ordercol is null then 0 else 1, ordercol ? saves a weird bug when someone puts values below -100 in ordercol.

              – mvds
              Sep 19 '12 at 21:36





              why not order by case when ordercol is null then 0 else 1, ordercol ? saves a weird bug when someone puts values below -100 in ordercol.

              – mvds
              Sep 19 '12 at 21:36













              That's a good point. I just wrote a vague example off the top of my head because we don't know what the data looks like. But your correction works better in general - applied it!

              – Blorgbeard
              Sep 19 '12 at 21:38





              That's a good point. I just wrote a vague example off the top of my head because we don't know what the data looks like. But your correction works better in general - applied it!

              – Blorgbeard
              Sep 19 '12 at 21:38











              2














              I ran into the same problem.
              I found out this could work:



              (I didn't find any isnull function for SQLite)



              order by ifnull(column_what_you_want_to_sort,'value in case of null')





              share|improve this answer






























                2














                I ran into the same problem.
                I found out this could work:



                (I didn't find any isnull function for SQLite)



                order by ifnull(column_what_you_want_to_sort,'value in case of null')





                share|improve this answer




























                  2












                  2








                  2







                  I ran into the same problem.
                  I found out this could work:



                  (I didn't find any isnull function for SQLite)



                  order by ifnull(column_what_you_want_to_sort,'value in case of null')





                  share|improve this answer















                  I ran into the same problem.
                  I found out this could work:



                  (I didn't find any isnull function for SQLite)



                  order by ifnull(column_what_you_want_to_sort,'value in case of null')






                  share|improve this answer














                  share|improve this answer



                  share|improve this answer








                  edited Aug 26 '17 at 12:59









                  lenooh

                  5,96553433




                  5,96553433










                  answered Apr 1 '16 at 13:13









                  linczylinczy

                  9614




                  9614























                      0














                      My solution was to reverse the range of my priority column and use order by desc (sort in descending order). This puts nulls at the end in one go.



                      Clearly this only works if you control your priority ordering column, but it's pretty great if you can swing it!



                      If that's doesn't work, I found a union query worked. Simply union two queries and add the constraint where not null on the first and where null on the second. The second query will be at the end of the first.






                      share|improve this answer




























                        0














                        My solution was to reverse the range of my priority column and use order by desc (sort in descending order). This puts nulls at the end in one go.



                        Clearly this only works if you control your priority ordering column, but it's pretty great if you can swing it!



                        If that's doesn't work, I found a union query worked. Simply union two queries and add the constraint where not null on the first and where null on the second. The second query will be at the end of the first.






                        share|improve this answer


























                          0












                          0








                          0







                          My solution was to reverse the range of my priority column and use order by desc (sort in descending order). This puts nulls at the end in one go.



                          Clearly this only works if you control your priority ordering column, but it's pretty great if you can swing it!



                          If that's doesn't work, I found a union query worked. Simply union two queries and add the constraint where not null on the first and where null on the second. The second query will be at the end of the first.






                          share|improve this answer













                          My solution was to reverse the range of my priority column and use order by desc (sort in descending order). This puts nulls at the end in one go.



                          Clearly this only works if you control your priority ordering column, but it's pretty great if you can swing it!



                          If that's doesn't work, I found a union query worked. Simply union two queries and add the constraint where not null on the first and where null on the second. The second query will be at the end of the first.







                          share|improve this answer












                          share|improve this answer



                          share|improve this answer










                          answered Feb 2 at 18:33









                          Evan MoranEvan Moran

                          2,5682616




                          2,5682616






























                              draft saved

                              draft discarded




















































                              Thanks for contributing an answer to Stack Overflow!


                              • Please be sure to answer the question. Provide details and share your research!

                              But avoid



                              • Asking for help, clarification, or responding to other answers.

                              • Making statements based on opinion; back them up with references or personal experience.


                              To learn more, see our tips on writing great answers.




                              draft saved


                              draft discarded














                              StackExchange.ready(
                              function () {
                              StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f12503120%2fhow-to-do-nulls-last-in-sqlite%23new-answer', 'question_page');
                              }
                              );

                              Post as a guest















                              Required, but never shown





















































                              Required, but never shown














                              Required, but never shown












                              Required, but never shown







                              Required, but never shown

































                              Required, but never shown














                              Required, but never shown












                              Required, but never shown







                              Required, but never shown







                              Popular posts from this blog

                              Monofisismo

                              Angular Downloading a file using contenturl with Basic Authentication

                              Olmecas