Question 90695 in PostgreSQL Question posted in
The official documentation can be found here.

Multicolumn order by a tuple – Postgresql

Lets supose I have a tabla A like:

bisac1 bisac2 bisac3 desire
x y z 10
y z x 8
z y x 6
x y p 20
r y z 13
x s z 1
a y l 12
a x k 2
x p w 1

I would like to be able to count the number of times any of these elements (x,y,z) appears in the cols (bisac1,bisac2,bisac3).

So, the expected result should be 3 for the first 3 rows, 2 for the next 3 and 1 for the last 3.

Tags:

2

Answers


  1. 0

    Seems the following should do what you require?

    select 
    case when bisac1 in ('x','y','z') then 1 else 0 end +
    case when bisac2 in ('x','y','z') then 1 else 0 end +
    case when bisac3 in ('x','y','z') then 1 else 0 end 
from t;
    Login or Signup to reply.
  2. 0

    You can also use one case per letter instead of one case per column (Stu’s approach). The result will be the same for your sample data:

    SELECT 
  CASE WHEN 'x' IN (bisac1, bisac2, bisac3) THEN 1 ELSE 0 END +
  CASE WHEN 'y' IN (bisac1, bisac2, bisac3) THEN 1 ELSE 0 END +
  CASE WHEN 'z' IN (bisac1, bisac2, bisac3) THEN 1 ELSE 0 END
FROM yourtable;

    The result will not be the same if the same letter occurs in different columns, For example, if your row looks like this:

    bisac1 bisac2 bisac3
    x y y

    Then Stu’s query will produce 3 as result, my query here 2. From your description, it is unclear to me if your sample data can contain such rows at all or if the two queries will always create the same result for your data.

    And even if your data can include such rows, it’s still unclear to me whether you want to get 3 or 2 as result.

    So, summarized, it’s up to you what exactly you want to use here.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search