Return column name when the min value matches
264 Репутация автора
I have a table that looks like this -
ZIP DC1 DC2 DC3 Min Min(DC) 1 2 3 4 2 DC1 2 5 4 3 3 DC3
Basically, I want to populate the last column
Min(DC). That column should show respective DC of the
Min column. I have been doing it with excel y using vlookp but since my data set has grown, I need to get this done in Teradata or any other sql based format.
865321 Репутация автора
Here is one method:
select t.*, least(dc1, dc2, dc3) as "Min", (case least(dc1, dc2, dc3) when dc1 then 'dc1' when dc2 then 'dc2' when dc3 then 'dc3' end) as MinCol
If you want to return all columns with the minimum value:
Автор: Gordon Linoff Размещён: 09.11.2017 01:33
select t.*, least(dc1, dc2, dc3) as "Min", ((case dc1 = least(dc1, dc2, dc3) then 'dc1;' else '' end) || (case dc2 = least(dc1, dc2, dc3) then 'dc2;' else '' end) || (case dc3 = least(dc1, dc2, dc3) then 'dc3;' else '' end) || ) as MinCols . . .
1987 Репутация автора
As a note, in Teradata, you can reference aliased columns in the same query. I've noticed you can't do that as easily in Postgres, which has become a pain in the butt. Re-writing the previous query:
SELECT t.*, LEAST(dc1, dc2, dc3) AS MyMin, ((CASE dc1 = MyMin THEN 'dc1;' ELSE '' END) || (CASE dc2 = MyMin THEN 'dc2;' ELSE '' END) || (CASE dc3 = MyMin THEN 'dc3;' ELSE '' END) || ) AS MinCols
I haven't checked if this works, but you get the idea.
As for dealing with the case of dc1 = dc3, you can do something like:
CASE WHEN dc1 = dc3 THEN dc1 || '_' || dc3 ELSE <blah> END AS <MyExpressionAlias>
|| is the concatenate character in SQL
But it looks like the code the previous guy provided will already return what you want if multiple columns share the MinValue.Автор: ravioli Размещён: 12.11.2017 03:03