CODEWITHSUNDEEP
postgresql
Sigularity
Sigularity

Reputation: 967

How to sort rows for each group ( PostgreSQL )

There is a group number and each group has several inst number. I need to sort the query result based on the status value. In case the value of status column within a group is 'ABNORMAL', the total rows belong to the group should be printed above like the desired result. I thought of UNION ALL but I don't see the correct one.

Can you help me on this matter?

@ DDL / DML

CREATE TABLE test.sort_test
(
    group_number integer,
    inst_number integer,
    status1 character varying,
    status2 character varying       
);

INSERT INTO test.sort_test VALUES(0,0,'NORMAL','NORMAL');
INSERT INTO test.sort_test VALUES(0,1,'NORMAL','NORMAL');
INSERT INTO test.sort_test VALUES(0,2,'NORMAL','NORMAL');
INSERT INTO test.sort_test VALUES(0,3,'NORMAL','NORMAL');
INSERT INTO test.sort_test VALUES(1,0,'ABNORMAL','NORMAL');
INSERT INTO test.sort_test VALUES(1,1,'NORMAL','NORMAL');
INSERT INTO test.sort_test VALUES(1,2,'NORMAL','NORMAL');
INSERT INTO test.sort_test VALUES(2,0,'NORMAL','ABNORMAL');

@ Original Query

select * 
from test.sort_test
order by group_number, inst_number

0   0   "NORMAL"    "NORMAL"
0   1   "NORMAL"    "NORMAL"
0   2   "NORMAL"    "NORMAL"
0   3   "NORMAL"    "NORMAL"
1   0   "ABNORMAL"  "NORMAL"
1   1   "NORMAL"    "NORMAL"
1   2   "NORMAL"    "NORMAL"
2   0   "NORMAL"    "ABNORMAL"

@ Desired result

1   0   "ABNORMAL"  "NORMAL"
1   1   "NORMAL"    "NORMAL"
1   2   "NORMAL"    "NORMAL"
2   0   "NORMAL"    "ABNORMAL"          
0   0   "NORMAL"    "NORMAL"
0   1   "NORMAL"    "NORMAL"
0   2   "NORMAL"    "NORMAL"
0   3   "NORMAL"    "NORMAL"

Upvotes: 0

Views: 51

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125204

select group_number, inst_number, status1, status2
from
    sort_test
    inner join (
        select group_number, bool_or('ABNORMAL' in (status1, status2)) as abnormal
        from sort_test
        group by group_number
    ) s using (group_number)
order by not abnormal, group_number, inst_number
;
 group_number | inst_number | status1  | status2
--------------+-------------+----------+----------
            1 |           0 | ABNORMAL | NORMAL
            1 |           1 | NORMAL   | NORMAL
            1 |           2 | NORMAL   | NORMAL
            2 |           0 | NORMAL   | ABNORMAL
            0 |           0 | NORMAL   | NORMAL
            0 |           1 | NORMAL   | NORMAL
            0 |           2 | NORMAL   | NORMAL
            0 |           3 | NORMAL   | NORMAL

bool_or is true if the condition is true in any of the rows of the group.

Upvotes: 2

Related Questions