CODEWITHSUNDEEP
postgresqlpostgresql-9.1
Sabuj Hassan
Sabuj Hassan

Reputation: 39385

Overlapping condition for case-when

I have the following query:

SELECT case     
 when tbl.id % 2 = 0 then 'mod-2'
 when tbl.id % 3 = 0 then 'mod-3'
 when tbl.id % 5 = 0 then 'mod-5'
 else 'mod-x'
 end as odds, tbl.id from some_xyz_table tbl;

If the table has Id 5,6,7 then it is returning output as (copied from pg-admin):

"mod-5";5
"mod-2";6
"mod-x";7

But, here I can see 6 is divisible by both 2 and 3. And my expected output is:

"mod-5";5
"mod-2";6
"mod-3";6   <-- this
"mod-x";7

Is there any way to modify this query to obtain such output? Any alternate solution will do for me.

Upvotes: 0

Views: 1015

Answers (1)

mlinth
mlinth

Reputation: 3118

You could do this with UNION queries [EDIT changed it to use UNION ALL]:

SELECT 'mod-5', id FROM tbl -- divisible by 5
WHERE id %5 = 0
UNION ALL
SELECT 'mod-2', id FROM tbl -- divisible by 2
WHERE id %2 = 0
UNION ALL
SELECT 'mod-3', id FROM tbl -- divisible by 3
WHERE id %3 = 0
UNION ALL
SELECT 'mod-x',id FROM tbl  -- not divisible by 5,3 or 2
WHERE id %5 <> 0 AND id%2 <> 0 AND id % 3 <> 0

Upvotes: 1

Related Questions