Sql query - Order by number of true values in the columns-CodePudding

I have a table like this

I need to get the validity order by the number of true values in the last 4 columns. For e.g. the output of the below query should be

1110  // 4 true values
1001  // 2 true values
1000 // 1 true value

CodePudding user response：

You can use subquery;

SELECT subquery.VALIDITY,
concat(CAST ((subquery.TAXI   subquery.CAR   subquery.TRUCK   subquery.BIKE) AS TEXT) , ' True values') as COUNTTRUEVALUES
FROM (select VALIDITY,
CASE WHEN TAXI THEN 1 ELSE 0 END AS TAXI,
CASE WHEN CAR THEN 1 ELSE 0 END AS CAR,
CASE WHEN TRUCK THEN 1 ELSE 0 END AS TRUCK,
CASE WHEN BIKE THEN 1 ELSE 0 END AS BIKE
FROM YourTableName) as subquery
ORDER BY 2 DESC

output

VALIDITY COUNTTRUEVALUES

1110      4 True values
1001      2 True values
1000      1 True values

CodePudding user response：

Convert the boolean values to integers and order descending by their total:

SELECT *
FROM tablename
ORDER BY taxi::int   car::int   truck::int   bike::int DESC;

See the demo.

CodePudding user response：

What about something like this?

SELECT 
id,
validity,
taxi1   car1   truck1   bike1
FROM 
(
SELECT
id,
validity
CASE WHEN taxi = 'TRUE' THEN 1 ELSE 0 END taxi1,
CASE WHEN car = 'TRUE' THEN 1 ELSE 0 END car1,
CASE WHEN truck = 'TRUE' THEN 1 ELSE 0 END truck1,
CASE WHEN bike = 'TRUE' THEN 1 ELSE 0 END bike1
FROM table
)

CodePudding user response：

Treat the 4 columns as a tuple of booleans and sort DESC?

SELECT *
FROM tablename
ORDER BY (taxi, car, truck, bike) DESC;