47
Views
6
Comments
Solved
How to sort on a date field but ignore null dates?
Question

I can't believe it is so hard to find an answer to this question. I have an advanced SQL statement, and want to sort by a date field ASC, but I don't want the null dates to appear first. So how do I ignore null dates in the sorting?

Cheers,

Rank: #139
Solution

Hi Daniel,


You say you don't want the nulldates to appear first, but where do you want them, last ??  You can't just "ignore" the records with a null date, you'll have to determine where you want them to go instead of first.


Just to offer an alternative to Daniels solution, you could add an extra calculated field to your aggregate or sql, and make that value "Z" when date is empty, and make it "A" for all other records.  Then put this as first column in your order by clause.

Also, as you are talking about an advanced sql, not an aggregate, you could use a union.


Dorine

mvp_badge
MVP
Rank: #19

Hi Daniel,

The reason why it is hard to find an answer is easy to explain.

Let's make it a text attribute that you want to sort alphabetically but want to skip the empty text? That would be difficult too. The problem is that the empty date or empty text is in ascending sort order the first element.

The easiest way would be to filter out the records with the date equal to NullDate().

A slightly more complicated solution is that you have two aggregates, one with only the records that have the null dates, and one that excludes them.

Then append the list of the aggregate with the null dates to the list of the aggregate (desc sorted) that excludes the null dates. The result list you can then bind to your table records widget lists records widget or other widgets that accept a list of records.

Regards,

Daniel

Rank: #139
Solution

Hi Daniel,


You say you don't want the nulldates to appear first, but where do you want them, last ??  You can't just "ignore" the records with a null date, you'll have to determine where you want them to go instead of first.


Just to offer an alternative to Daniels solution, you could add an extra calculated field to your aggregate or sql, and make that value "Z" when date is empty, and make it "A" for all other records.  Then put this as first column in your order by clause.

Also, as you are talking about an advanced sql, not an aggregate, you could use a union.


Dorine

Rank: #16283

Hi Daniel,

Not sure about your use case yet.

As mentioned by Dorine, does it mean that you want the null date record to be sorted to the bottom of the list? 

If so, why not use two aggregates to sort them? (since OS usually advise people to use aggregate as much as possible)


The first aggregate that filter <> nulldate() records and sort by date asc, while the second aggregate filter = nulldate(). 

After that, you can append the 1st aggregate with the 2nd aggregate results. 

With this method, you can get the list sorted by Date ASC while your null date records appear at the bottom of your list. 


Pardon my poor English. 


Regards
Olivier Cheah

mvp_badge
MVP
Rank: #129

Daniel McMechan wrote:

I can't believe it is so hard to find an answer to this question. I have an advanced SQL statement, and want to sort by a date field ASC, but I don't want the null dates to appear first. So how do I ignore null dates in the sorting?

Cheers,


Hi Daniel,


Please refer below snap and use to solve your issue,

Rank: #17597

Thanks all for you input. I ended up adding a 'calculated' field to the query as Dorine suggested. Appreciate the help.

Dan