Table name: employment_by_industry
For each row, I want to select the column name that has the maximum value:
I’m looking for succinct ways to do that in Oracle, with the column names only being listed once in the SQL query.
Here is a PostgreSQL technique:
select objectid, (SELECT industry FROM (values
(agr_forest_fish, 'agr_forest_fish'),
(mining_quarry, 'mining_quarry'),
(mfg, 'mfg'),
(electric, 'electric'),
(water_sew, 'water_sew')
) as t(emp, industry)
order by emp DESC
LIMIT 1)
from employment_by_industry
Source
https://dbfiddle.uk/ZdJhvNZC
Technically, I suppose the column names are listed more than once. But maybe it’s close enough.
Can the PostgreSQL query be adapted to work in Oracle? (any version)
On Oracle, assuming each row would always have just a single maximum employment value, we can use a GREATEST() trick here:
WITH cte AS (
SELECT t.*,
GREATEST(agr_forest_fish, mining_quarry, mfg, electric, water_sew) AS max_val
FROM employment_by_industry t
)
SELECT
objectid,
CASE WHEN agr_forest_fish = max_val THEN 'agr_forest_fish'
WHEN mining_quarry = max_val THEN 'mining_quarry'
WHEN mfg = max_val THEN 'mfg'
WHEN electric = max_val THEN electric
WHEN water_sew = max_val THEN 'water_sew' END AS industry
FROM cte
ORDER BY objectid;