Sort a varchar in ascending order with nulls first

Moderator: NorbertKrupa

Post Reply
User avatar
usli06
Intermediate
Intermediate
Posts: 93
Joined: Wed Jan 25, 2012 4:53 am

Sort a varchar in ascending order with nulls first

Post by usli06 » Thu Aug 09, 2012 1:58 pm

Hi,

I would like to sort on a varchar column but have the rows having a null value for the sort column appear first in the result set as opposed to last.

I tried using NULLS LAST in the ORDER BY but that didn't work:

Code: Select all

dbadmin=> select my_data from null_sort_test order by my_data nulls last;
ERROR:  syntax error at or near "nulls" at character 53
LINE 1: ...lect my_data from null_sort_test order by my_data nulls last...
I can do a ORDER BY DESC to get the NULLS first but I don't want all the data in reverse order! Plus I've seen in other posts there is a huger performance hit when sorting descending.

For instance:

Code: Select all

------------------------------
 QUERY PLAN DESCRIPTION:
 ------------------------------

 explain select my_data from null_sort_test order by my_data;

 Access Path:
 +-STORAGE ACCESS for null_sort_test [Cost: 649, Rows: 10K (NO STATISTICS)] (PATH ID: 2)
 |  Projection: test.null_sort_test_super
 |  Materialize: null_sort_test.my_data
 |  Execute on: Query Initiator


 ------------------------------
 QUERY PLAN DESCRIPTION:
 ------------------------------

 explain select my_data from null_sort_test order by my_data desc;

 Access Path:
 +-SORT [Cost: 1K, Rows: 10K (NO STATISTICS)] (PATH ID: 1)
 |  Order: null_sort_test.my_data DESC
 |  Execute on: Query Initiator
 | +---> STORAGE ACCESS for null_sort_test [Cost: 649, Rows: 10K (NO STATISTICS)] (PATH ID: 2)
 | |      Projection: test.null_sort_test_super
 | |      Materialize: null_sort_test.my_data
 | |      Execute on: Query Initiator
Anyone know how I can sort a varchar in ascending order by place the nulls first?

Thanks in advance!

Rick
Newbie
Newbie
Posts: 23
Joined: Thu Jul 26, 2012 2:46 pm

Re: Sort a varchar in ascending order with nulls first

Post by Rick » Thu Aug 09, 2012 2:22 pm

You could use group by to group the nulls together separately from the rest, then order them first

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: Sort a varchar in ascending order with nulls first

Post by id10t » Thu Aug 09, 2012 6:33 pm

Hi!

You need simple map/function.
Example:
  • Table

    Code: Select all

    CREATE TABLE public.nulls
    (
        val varchar(3)
    );
    
  • Data

    Code: Select all

    test_db=> \pset null 'NULL'
    Null display is "NULL".
    test_db=> select * from nulls order by 1;
     val  
    ------
     aaa
     bbb
     ccc
     NULL
     NULL
     NULL
    (6 rows)
  • Map/function:

    Code: Select all

    test_db=> select * from nulls order by (val is not NULL)::int, 1;
     val  
    ------
     NULL
     NULL
     NULL
     aaa
     bbb
     ccc
    (6 rows)
    
  • Test our function:

    Code: Select all

    test_db=> select * from nulls order by (val is NOT NULL)::int, val DESC;
     val  
    ------
     NULL
     NULL
     NULL
     ccc
     bbb
     aaa
    (6 rows)
Explain: I cast boolean expression (<column> is NOT NULL) to INT. If column is NULL so value of expression is 0, otherwise is 1. Since order by default is ASC so 0 is "before" 1 and after it i sort by <column>

Code: Select all

test_db=> select (val is NOT NULL)::int as 'is not null', val from nulls order by 1 asc, 2 asc;
 is not null | val  
-------------+------
           0 | NULL
           0 | NULL
           0 | NULL
           1 | aaa
           1 | bbb
           1 | ccc
(6 rows)
PS Its just example - dont take it as copy/paste solution may be there are exists a better solution.
Without casting works too:

Code: Select all

test_db=> select val is NOT NULL as 'is not null', val from nulls order by 1 asc, 2 asc;
 is not null | val  
-------------+------
 f           | NULL
 f           | NULL
 f           | NULL
 t           | aaa
 t           | bbb
 t           | ccc
(6 rows)

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Sort a varchar in ascending order with nulls first

Post by JimKnicely » Thu Aug 09, 2012 7:02 pm

Great suggestions!

Here's another one along the same line as sKwa's:

Code: Select all

dbadmin=> select my_data from null_sort_test order by nvl2(my_data, 1, 0), my_data;
 my_data
---------


 test1
 test2
 test3
(5 rows)

Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “Vertica SQL”