community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
#SANTALYTICS

Gather all 9 clues to complete the final Weekly Challenge on Dec 16!

Learn More

Group By and Sort with NULL Values Last

Meteor

Is there an easy way to do a group by and sort so that the NULL values go to the end of each group rather than the beginning?  Example via SQL:

 

select col
from table
group by col
order by col NULLS LAST

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

@mark_cyr,

 

Quick thought (might work):

 

Create a field:  IsNull as:

IIF(IsNull([col]),1,0)

Sort by:  IsNull (Ascending), Col (Ascending)

 

Use a SELECT to get rid of the IsNull Column.

 

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Meteor

I forgot to mention that I'm trying to do this across a large number of columns.  There are nulls present in several of them.  

 

I hacked together something similar to your idea @MarqueeCrew. I used the multi-field formula tool and and replaced all NULL value across all of those columns with a fixed value that would sort last (depending on whether it was alpha or numeric).  I then did a group by / sort. After that, I used the multi-field formula again to replace the fixed value with NULL again. I don't like doing this like this and changing my input data, but the idea you suggested would require a number of helper columns.  

Labels