Sort by Column Asc, But Null Values First

Sort by column ASC, but NULL values first?

Postgres has the NULLS FIRST | LAST modifiers for ORDER BY expression:

... ORDER BY last_updated NULLS FIRST

The typical use case is with descending sort order (DESC), which produces the complete inversion of the default ascending order (ASC) with null values first - which is often not desirable. To sort NULL values last:

... ORDER BY last_updated DESC NULLS LAST

To support the query with an index, make it match:

CREATE INDEX foo_idx ON tbl (last_updated DESC NULLS LAST);

Postgres can read btree indexes backwards, but for some query plans it matters where NULL values are appended. See:

• Performance impact of view on aggregate function vs result set limiting

How to sort a column in ascending order with NULL values at the end?

If you calculate anything on the date field, you'll loose the use of the index on that.

So check for Null:

SELECT 
    startdate, id
FROM 
    YourTable
ORDER BY
    StartDate Is Null,
    StartDate

Why do NULL values come first when ordering DESC in a PostgreSQL query?

Actually, with default sort order (ASCENDING) NULL values come last.

Logic dictates that the sort order be reversed with the DESCENDING keyword, so NULLs come first in this case.

But the best part comes last: you can choose which way you want it:

• Use the NULLS FIRST | LAST clause.

Quoting the current manual, version 9.3 as of writing:

If NULLS LAST is specified, null values sort after all non-null
values; if NULLS FIRST is specified, null values sort before all
non-null values. If neither is specified, the default behavior is
NULLS LAST when ASC is specified or implied, and NULLS FIRST when DESC
is specified (thus, the default is to act as though nulls are larger
than non-nulls). When USING is specified, the default nulls ordering
depends on whether the operator is a less-than or greater-than operator.

Bold emphasis mine.

SQL how to make null values come last when sorting ascending

select MyDate
from MyTable
order by case when MyDate is null then 1 else 0 end, MyDate

order by nulls first in descending order in laravel

In an ascending sort, null values appear last by default (and first in a descending sort). Postgres provide a way to override the default sort ordering null with option nulls first and nulls last.

You could use it with orderByRaw:

Source::orderByRaw('last_rank_update nulls first')

Sort NULL values to the end of a table

NULL values are sorted last in default ascending order. You don't have to do anything extra.

The issue applies to descending order, which is the perfect inverse and thus sorts NULL values on top.

PostgreSQL 8.3 introduced NULLS LAST:

ORDER BY somevalue DESC NULLS LAST

For PostgreSQL 8.2 and older or other RDBMS without this standard SQL feature:

ORDER BY (somevalue IS NULL), somevalue DESC

FALSE sorts before TRUE, so NULL values come last, just like in the example above.

See:

• Sort by column ASC, but NULL values first?
• The manual on SELECT

order by nulls first in descending order

Yup, I think you're looking for:

Book.order('ordering ASC NULLS FIRST, created_at DESC')

This will sort by ordering, and then by created_at in results that have the same ordering.

NULL sorting in BigQuery

Since May 1st 2020, NULLS FIRST and NULLS LAST feature is available

https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#order_by_clause

Your example can be expressed as

SELECT * FROM books
ORDER BY books ASC NULLS FIRST

and

SELECT * FROM books
ORDER BY books ASC NULLS LAST

Sorting a DGV column ascending while putting null values at the bottom

String sorting

What you're asking can be done by creating a custom row comparer for your DataGridView (adapted version of MSDN's code):

Public Class DataGridViewColumnComparer
    Implements IComparer

    Private SortOrderModifier As Integer = 1
    Private ColumnName As String

    Public Sub New(ByVal ColumnName As String, ByVal Order As SortOrder)
        Me.ColumnName = ColumnName
        If Order = SortOrder.Descending Then
            SortOrderModifier = -1
        ElseIf Order = SortOrder.Ascending Then
            SortOrderModifier = 1
        End If
    End Sub

    Public Function Compare(ByVal x As Object, ByVal y As Object) As Integer Implements IComparer.Compare
        Dim Row1 As DataGridViewRow = CType(x, DataGridViewRow)
        Dim Row2 As DataGridViewRow = CType(y, DataGridViewRow)

        Dim Row1Value As String = Row1.Cells(ColumnName).Value.ToString()
        Dim Row2Value As String = Row2.Cells(ColumnName).Value.ToString()

        'If CompareResult = 1 that means that Row1 should be placed BELOW Row2.
        'If CompareResult = -1 that means that Row1 should be placed ABOVE Row2.
        Dim CompareResult As Integer = String.Compare(Row1Value, Row2Value)

        If String.IsNullOrEmpty(Row1Value) = True Then
            CompareResult = 1 'Row1 has an empty/null value, place it below Row2.
        ElseIf String.IsNullOrEmpty(Row2Value) = True Then
            CompareResult = -1 'Row2 has an empty/null value, place Row1 above.
        End If

        Return CompareResult * SortOrderModifier
    End Function
End Class

Usage:

DataGridView1.Sort(New DataGridViewColumnComparer("Height", SortOrder.Ascending))

And it works!

---

Numerical sorting

The previous example showed a string-based sorting algorithm. It'll sort each cell character-by-character based on which character comes first in the charset.

If you instead want to sort the rows numerically you'd have to adapt the code a little more:

Public Class DataGridViewNumericComparer
    Implements IComparer

    Private SortOrderModifier As Integer = 1
    Private ColumnName As String

    Public Sub New(ByVal ColumnName As String, ByVal Order As SortOrder)
        Me.ColumnName = ColumnName
        If Order = SortOrder.Descending Then
            SortOrderModifier = -1
        ElseIf Order = SortOrder.Ascending Then
            SortOrderModifier = 1
        End If
    End Sub

    Public Function Compare(ByVal x As Object, ByVal y As Object) As Integer Implements IComparer.Compare
        Dim Row1 As DataGridViewRow = CType(x, DataGridViewRow)
        Dim Row2 As DataGridViewRow = CType(y, DataGridViewRow)

        Dim Row1Value As String = Row1.Cells(ColumnName).Value.ToString()
        Dim Row2Value As String = Row2.Cells(ColumnName).Value.ToString()

        Dim Row1NumVal As Nullable(Of Long) = Nothing
        Dim Row2NumVal As Nullable(Of Long) = Nothing

        Dim Row1Temp As Long = 0
        Dim Row2Temp As Long = 0

        If Long.TryParse(Row1Value, Row1Temp) = True Then Row1NumVal = Row1Temp
        If Long.TryParse(Row2Value, Row2Temp) = True Then Row2NumVal = Row2Temp

        'If CompareResult = 1 that means that Row1 should be placed BELOW Row2.
        'If CompareResult = -1 that means that Row1 should be placed ABOVE Row2.
        Dim CompareResult As Integer

        If Row1NumVal.HasValue = True AndAlso Row2NumVal.HasValue = True Then
            CompareResult = Row1NumVal.Value.CompareTo(Row2NumVal.Value)

        ElseIf Row1NumVal.HasValue = False Then
            CompareResult = 1

        ElseIf Row2NumVal.HasValue = False Then
            CompareResult = -1

        End If

        Return CompareResult * SortOrderModifier
    End Function
End Class

Usage:

DataGridView1.Sort(New DataGridViewNumericComparer("Height", SortOrder.Ascending))

This version will sort all numbers in numerical order and place everything that is not a number at the bottom.

---

Related Topics