Oracle order by descending with NULL last

Question

my objective is, to print the result of the query in "DESCENDING" order. but the problem is, the rows with NULL values went on top of the list.. how to put the null rows at the bottom, if the order by is descending?

select mysubcat.subcat
       , mysubcat.subcatid as subcat_id
       , (select SUM(myad.PAGEVIEW) 
           from myad 
            where MYAD.CREATEDDATE between  '01-JUL-13 02.00.49.000000000 PM' and '13-JUL-13 02.00.49.000000000 PM'
            AND MYAD.status = 1 
            and  MYAD.mobileapp IS NULL
            and myad.subcatid = mysubcat.subcatid )as web_views 
from mysubcat 
order by web_views desc;

the sample result goes like this

                             SUBCAT_ID    WEB_VIEWS
Swimming Lessons                56        (null)    
Medical Services                17        (null)
Mobile Phones & Tablets         39        6519
Home Furnishing & Renovation   109        4519

the order is in the descending order, I just want to put the rows with null values at the bottom of the printed result, so how?

score 31 · Accepted Answer · edited Jun 20 '20 at 09:12

31

You can use DESC NULLS LAST to achieve that.

Here is the official documentation from Oracle.

NULLS LAST

Specifies that NULL values should be returned after non-NULL values.

edited Jun 20 '20 at 09:12

Community

1
1

answered Jul 14 '13 at 13:33

AllTooSir

48,828
16
130
164

2

@sasori - but it would have been even faster for you to have read the documentation yourself. – APC Jul 14 '13 at 14:52
@APC That appears to be the documentation for "JavaDB", not the Oracle database. It does happen to be the same in this case, but [Oracle's actual documentation](http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_10002.htm#i2171079) tends to be rather impenetrable. [PSOUG](http://psoug.org/reference/orderby.html) tends to be more helpful, but even they're pretty verbose at times. – jpmc26 Nov 13 '13 at 04:19
@jpmc26 - PSOUG has been in decline since Dan Morgan stopped maintaining it. For up-to-date stuff you should use his new sight: http://www.morganslibrary.org/library.html – APC Nov 13 '13 at 05:33
@APC That may not be true if you're still on Oracle 10g like my client. (They're currently in the process of upgrading to 11g.) – jpmc26 Jan 20 '14 at 20:24

score 7 · Answer 2 · answered Jul 14 '13 at 13:32

7

Use a case

order by case when web_views is not null 
              then 1 
              else 2 
         end asc, 
         web_views desc;

answered Jul 14 '13 at 13:32

juergen d

201,996
37
293
362

thanks , I'll choose the first answer as the best though, coz it doesn't require any conditional statement to achieve the goal – sasori Jul 14 '13 at 13:35
3

+1 . . . I prefer this version because it works in more databases. – Gordon Linoff Jul 14 '13 at 14:14
You saved my and my collegues day! – Cyrille Pontvieux Sep 22 '15 at 14:33

Oracle order by descending with NULL last

2 Answers2