Вопрос:

Return column name when the min value matches

sql teradata

60 просмотра

2 ответа

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.

Автор: Karvy1 Источник Размещён: 08.11.2017 11:31

Ответы (2)


1 плюс

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:

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
. . .
Автор: Gordon Linoff Размещён: 09.11.2017 01:33

1 плюс

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
Вопросы из категории :
32x32