Grouping select output by null value in PostgreSQL

Question

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;

Lukasz Szozda · Accepted Answer · 2016-05-15 06:33:04Z

2

You could use IS NULL:

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

SqlFiddleDemo

edited May 15, 2016 at 6:33

answered May 15, 2016 at 6:24

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user330315 · Accepted Answer · 2016-05-15 06:49:34Z

1

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

answered May 15, 2016 at 6:49

user330315

1 Comment

Lukasz Szozda Over a year ago

NULLS FIRST is nice option, but check demo last two records, OP wants grouping by NULL/NOT NULL and order by trx DESC

Bavishi PN · Accepted Answer · 2016-05-15 05:22:25Z

0

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

answered May 15, 2016 at 5:22

Bavishi PN

3892 silver badges6 bronze badges

Collectives™ on Stack Overflow

Grouping select output by null value in PostgreSQL

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related