PostgreSQL, SQL: Compare values in one column in the table and return multiple columns by the condition

Question

I have a table with a few rows. I need to check: if all the values in the column index are equal then get three columns with unique values, If values are not equal then get an empty table. For example if index are equal: Input

index   Product Version
10        A      2.5
10        A      2.5  
10        A      2.5

Output

index   Product Version
10        A      2.5

For example if index are not equal: Input:

index   Product      Version
10          A          2.5
11          B          3.3
10          A          2.5
10          A          2.5

Output

index   Product      Version

I tried to do that with "Case When" statement, but "Case When" can return only single column. Is there a way to compare values in a column and return several columns?

. . Your question would be clearer if you had an example that returned more than one row. — Gordon Linoff
– Gordon Linoff, Commented Mar 11, 2021 at 12:30

S-Man · Accepted Answer · 2021-03-11 10:31:50Z

1

demo: all records equal

demo: records not equal

SELECT
    *
FROM (
    SELECT
        *,
        row_number() OVER (PARTITION BY index, product, version),
        count(*) OVER ()
    FROM mytable
) s
WHERE row_number = count

Add a row count to each column group. If all records equal, the maximum row count equals the all-over record count.

So just return the record which have the same row number as the total count. If not all records equal, this record would not exist and return an empty table.

answered Mar 11, 2021 at 10:31

S-Man

24k9 gold badges51 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2021-03-11 12:29:51Z

1

Hmmm . . . this may be what you are thinking:

select distinct index, product, version
from t
where not exists (select 1
                  from t t2
                  where t2.index <> t.index
                 );

answered Mar 11, 2021 at 12:29

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Collectives™ on Stack Overflow

PostgreSQL, SQL: Compare values in one column in the table and return multiple columns by the condition

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related