how to select from case name in postgresql-CodePudding

I use postgres v14, I create a postgres view it contain 5 time columns.

Every user id have a date in side one column

it mean every userid have more than 4 rows

i want to get one row have greatest row

my cols is (id,created_at, newstage_created_at ,newstage_updated_at,newdata_created_at) look like

id	created_at	newstage_created_at	newstage_updated_at	newdata_created_at
1	2020-11-23 11:28:05	2020-11-23 11:28:05	2020-11-23 11:28:05	null
1	2020-11-23 11:28:05	2020-11-23 11:28:05	2020-11-24 08:13:48	2020-11-25 08:13:48
1	2020-11-23 11:28:05	2020-10-11 05:55:36	2021-01-11 05:55:36	2020-10-11 05:55:36
2	2022-02-09 18:41:42	2022-02-09 18:41:42	2022-02-09 18:41:42	2022-02-09 18:41:42
2	2022-02-09 18:41:42	2022-02-09 18:41:42	2022-02-09 18:41:42	null
2	2022-02-09 18:41:42	2020-10-12 09:55:31	2022-02-09 18:41:42	2022-02-09 18:41:42
2	2022-02-09 18:41:42	2020-10-12 09:55:31	2022-02-09 18:41:42	2022-02-09 18:41:42

here i need to get the row have greatest column and greatest row

i did this sql

case greatest(created_at, newstage_created_at ,newstage_updated_at,newdata_created_at)
when created_at then 'created_at'
when newstage_created_at then 'newstage_created_at'
when newstage_updated_at then 'newstage_updated_at'
when newdata_created_at then 'newdata_created_at'
else null
end greatestcolumn
FROM people_count_view 
WHERE 
annonce_id='1'
and 
(id,greatestcolumn) in (select id,greatestcolumn from people_count_view)
order by id

of course greatestcolumn is not column name, it was bad attemp

CodePudding user response：

Not sure that I understand your task correct, but you can take this code and modify (in generally it seems like what you want to get).

with t as (
select id,
       max(created_at)::timestamp as max_created_at,
       max(newstage_created_at) as max_newstage_created_at,
       max(newstage_updated_at) as max_newstage_updated_at,
       max(newdata_created_at) as max_newdata_created_at
from test.people_count_view
GROUP BY id
order by id)
select
    id,
    greatest(max_created_at,
        max_newstage_created_at,
        max_newstage_updated_at,
        max_newdata_created_at) as greatestcolumn,
    case greatest(max_created_at,
        max_newstage_created_at,
        max_newstage_updated_at,
        max_newdata_created_at)
        when max_created_at then 'max_created_at'
        when max_newstage_created_at then 'max_newstage_created_at'
        when max_newstage_updated_at then 'max_newstage_updated_at'
        when max_newdata_created_at then 'max_newdata_created_at'
        end as greatestcolumn_name
    from t;

output result

Regarding your code - just remember not to use aliases (greatestcolumn) in WHERE conditions. In my case, I wrapped the query in a CTE to use it as column name. Other way - in WHERE block you need to use the same condition that you use in CASE with this alias