Sorting Null Values After All Others, Except Special

var arr = [5, 25, null, 1, null, 30]


arr.sort(function(a, b) {

  return (b != null) - (a != null) || a - b;

})


console.log(arr)

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

How to sort an EAV model that have null values?

Note: I'm still somewhat confused about what you want your resultset to look like.

select *
  from value v1
  left join value v2 on (v1.entity_id = v2.entity_id 
                         and v2.field_id = 2)
 where v1.field_id = 1 -- assuming all users have a name
 order by v2.value;

Result:

ENTITY_ID | FIELD_ID |     VALUE |  ENTITY_ID | FIELD_ID | VALUE
----------|----------|-----------|------------------------------
        1 |        1 |   "Alice" |          1 |        2 | "24"
        2 |        1 |     "Bob" |          2 |        2 | "27"
        3 |        1 | "Charlie" |       null |     null | null

---

Or if you prefer the names of the fields:

select v1.entity_id, f1.name, v1.value, f2.name, v2.value
  from value v1
  join field f1 on v1.field_id = f1.field_id
  left join value v2 on (v1.entity_id = v2.entity_id 
                         and v2.field_id = 2)
  left join field f2 on v2.field_id = f2.field_id                         
 where f1.name = 'firstname' -- assuming all users have a name
 order by v2.value;

Result:

ENTITY_ID | NAME        |     VALUE |  NAME | VALUE
----------|-------------|-----------|--------------
        1 | "firstname" |   "Alice" | "age" |  "24"
        2 | "firstname" |     "Bob" | "age" |  "27"
        3 | "firstname" | "Charlie" |  null |  null

---

For the case where there's an entity, that doesn't have a firstname, I added two rows to entity and a row to value:

insert into entity values (4);
insert into entity values (5);
insert into value values (4, 2, '102');

If a record exists in the entity table, it will always be in this result:

select *
  from entity e
  left join value v1 on (e.entity_id = v1.entity_id
                         and v1.field_id = 1) -- firstname
  left join value v2 on (e.entity_id = v2.entity_id 
                         and v2.field_id = 2) -- age
-- order by v2.value IS NULL; -- age nulls last

Result:

ENTITY_ID | ENTITY_ID |   FIELD_ID |     VALUE |  ENTITY_ID | FIELD_ID | VALUE
----------|-----------|------------|-----------|------------|----------|------
        1 |         1 |          1 |   "Alice" |          1 |        2 |  "24"
        2 |         2 |          1 |     "Bob" |          2 |        2 |  "27"
        4 |      null |       null |      null |          4 |        2 | "102"
        3 |         3 |          1 | "Charlie" |       null |     null |  null
        5 |      null |       null |      null |       null |     null |  null

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

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

LINQ: Sort records in ascending order with NULL values last

You could use int.MaxValue or something for those with NULL:

IEnumerable<EKGTypes> items = context.EKGTypes
                              .ToList()
                              .Select(c => MapEntity(c))
                              .OrderBy(c => 
                                  c.DisplayOrder.HasValue 
                                  ? (c.DisplayOrder == 0 ? (int.MaxValue - 1) : c.DisplayOrder) 
                                  : int.MaxValue)
                              .ToList();

So you effectively sort by DisplayOrder while the ones with DisplayOrder == null will be treated as if their DisplayOrder was int.MaxValue and the ones with DisplayOrder == 0 as if their DisplayOrder was int.MaxValue - 1.

So at first all non-NULL and not-0 will be sorted by their DisplayOrder, followed by the ones with 0 and finally the NULL values.

Replace NULL values per partition

The window function first_value() with the right ordering is probably cheapest:

SELECT session_id, step
     , COALESCE(device
              , first_value(device) OVER (PARTITION BY session_id ORDER BY device IS NULL, step)
               ) AS device
FROM   tbl
ORDER  BY session_id DESC, step;

db<>fiddle here

ORDER BY device IS NULL, step sorts NULL values last, so the earliest step with a notnull value is picked. See:

• Sorting null values after all others, except special

If notnull devices per session_id are always the same, you can simplify to just ORDER BY device IS NULL. And you don't need COALESCE.

PostgreSQL: order by column, with specific NON-NULL value LAST

Postgres allows boolean values in the ORDER BY clause, so here is your generalised 'X LAST':

ORDER BY (my_column = 'X')

The expression evaluates to boolean, resulting values sort this way:

FALSE (0)
TRUE (1)
NULL

Since we deal with non-null values, that's all we need. Here is your one-liner:

...
ORDER BY (zone = 'Future'), zone, status;

Related:

• Sorting null values after all others, except special
• Select query but show the result from record number 3
• SQL two criteria from one group-by

Sort list while pushing None values to the end

>>> l = [1, 3, 2, 5, 4, None, 7]
>>> sorted(l, key=lambda x: (x is None, x))
[1, 2, 3, 4, 5, 7, None]

This constructs a tuple for each element in the list, if the value is None the tuple with be (True, None), if the value is anything else it will be (False, x) (where x is the value). Since tuples are sorted item by item, this means that all non-None elements will come first (since False < True), and then be sorted by value.

---

Related Topics