CODEWITHSUNDEEP
sqldatabasepostgresqlpostgresql-9.5
Alan Wayne
Alan Wayne

Reputation: 5384

Grouping select output by null value in PostgreSQL

The simplified version is this: I have a table with two fields. The first field, trx, will always have a value. The second field, tstop, can be either null or a timestamp.

I would like to organize the output from the select such that the first "group" of records all have tstop of null, the remaining records have a non-null value of tstop. Each group is ordered by trx desc.

How is this done?

TABLE rx
(
  recid serial NOT NULL,
  trx timestamp without time zone NOT NULL,
  tstop timestamp without time zone
)

Example values:
recid    trx                      tstop
36;      "2014-06-10 13:05:16";   "";
113759;  "2014-06-10 13:05:16";   "";
33558;   "2014-03-31 18:08:15";   "2014-03-31 18:08:15";
12535;   "2014-03-31 18:08:15";   "";
660;     "2014-03-31 18:05:59";   "";
144209;  "2014-03-30 19:21:14";   "";

Desired Output:

 recid         trx                  tstop
 36;      "2014-06-10 13:05:16";   "";
 113759;  "2014-06-10 13:05:16";   "";
 12535;   "2014-03-31 18:08:15";   "";
 660;     "2014-03-31 18:05:59";   "";
 144209;  "2014-03-30 19:21:14";   "";
 33558;   "2014-03-31 18:08:15";   "2014-03-31 18:08:15";

This obviously will not work:

select * from rx order by trx desc;

Upvotes: 2

Views: 125

Answers (3)

user330315
user330315

Reputation:

Just order by the columns and use the option nulls first to make null values appear first:

SELECT *
FROM rx
ORDER BY tstop DESC NULLS FIRST, trx DESC

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175556

You could use IS NULL:

SELECT *
FROM rx
ORDER BY tstop IS NULL DESC, trx DESC

SqlFiddleDemo

Upvotes: 2

Bavishi PN
Bavishi PN

Reputation: 389

ORDER BY (CASE WHEN tstop IS NULL THEN 1 ELSE 0 END) DESC, tstop DESC

Upvotes: 0

Related Questions