Sorting Nulls

Results 1 to 2 of 2

Thread: Sorting Nulls

  1. #1
    Join Date
    Dec 1969

    Default Sorting Nulls

    I certainly have some sorting woes tonight.<BR><BR>I&#039;ve got several fields of data and when I sort it, I want the NULLS to go last when sorting Ascendingly. Unfortunately, they go first. Is there an easy solution around this that doesn&#039;t involve piping new values in place of the Null?

  2. #2
    Join Date
    Dec 1969

    Default You COULD do...

    SQL Server:<BR> ORDER BY CASE WHEN field IS NULL THEN 1 ELSE 0 END ASC, field ASC<BR><BR>Access:<BR> ORDER BY IIF( IsNull(field), 1, 0 ) ASC, field ASC<BR><BR>See the trick? The *first* "sort" will put the non-nulls ahead of all the nulls, because the nulls produce a value of 1 in the ORDERing where the non-nulls produce a value of zero. Then the second "sort" (on the field itself) works as you expect within all the non-null values.<BR><BR>You could also do<BR><BR>SQL Server:<BR> IsNull( field, 999999999 ) <BR><BR>Access:<BR> IIF( IsNull(field), 999999999, field )<BR><BR>which only involves "piping" in a new value for the purposes of the ORDER BY; it does *NOT* affect your SELECT statement, where you would still get NULL for field.<BR><BR>You choose an appropriate value to substitute for the null, of course, depending upon the field type. But the first version I gave you doesn&#039;t depend at all on the field type, so you can&#039;t make a mistake in choosing such a value.<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts