TransWikia.com

How do I Order By on multiple rows in Postgresql

Database Administrators Asked on December 17, 2021

I have a table with columns with severities like:

name   | critical | major | trivial 
----------------------------------------
Apache |2         |3      |2
Eclipse|5         |0      |4
TestNg |0         |0      |5
JUnit  |0         |2      |7

I want to list the rows in the order by their severities in descending order – critical, major and trivial.

For the above example, I’d like to get back:

name  | critical | major | trivial 
----------------------------------------
Eclipse|5         |0      |4
Apache |2         |3      |2
JUnit  |0         |2      |7
TestNg |0         |0      |5

critical has highest severity, then comes major and then trivial. Notice eclipse has to come first since 5 > 2 (Critical, internal sorting)

One Answer

Simply order them with the right orde4r with DESC for descending

Schema (PostgreSQL v9.6)

CREATE TABLE test (
  "name" VARCHAR(7),
  "critical" INTEGER,
  "major" INTEGER,
  "trivial" INTEGER
);

INSERT INTO test
  ("name", "critical", "major", "trivial")
VALUES
  ('Apache', '2', '3', '2'),
  ('Eclipse', '5', '0', '4'),
  ('TestNg', '0', '0', '5'),
  ('JUnit', '0', '2', '7');

Query #1

SELECT * FROM test
ORDER BY "critical" DESC, "major" DESC, "trivial" DESC;

| name    | critical | major | trivial |
| ------- | -------- | ----- | ------- |
| Eclipse | 5        | 0     | 4       |
| Apache  | 2        | 3     | 2       |
| JUnit   | 0        | 2     | 7       |
| TestNg  | 0        | 0     | 5       |

View on DB Fiddle

Answered by nbk on December 17, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP