CODEWITHSUNDEEP
sql
Eric Lindauer
Eric Lindauer

Reputation: 1837

dynamic selection in SQL aggregate function

Imagine I have a column with row data A, B, and C. When aggregating the column, I want B to show up if any row contains B, else C, else A.

Is this possible? I'm using vertica but would be curious to hear solutions for other data stores.

+------+-------+
| Col1 | Col 2 |
+------+-------+
| A    |   X   |
| B    |   X   |
| C    |   X   |
+------+-------+

SELECT ???(Col1), Col2 
FROM my_two_column_table
GROUP BY Col2

expected result: one row - B, X

Upvotes: 0

Views: 45

Answers (1)

JimmyB
JimmyB

Reputation: 12610

Something like this could work:

SELECT COALESCE( MAX(t2.value), MAX(t3.value), MAX(t4.value) )
FROM my_table t1
LEFT OUTER JOIN my_table t2 ON t2.value = 'B'
LEFT OUTER JOIN my_table t3 ON t3.value = 'C'
LEFT OUTER JOIN my_table t4 ON t4.value = 'A'
WHERE t1.value in ( 'A', 'B', 'C' )

The t1 table can also be replaced by dual or whatever dummy table with at least one row you have.

Upvotes: 2

Related Questions