CODEWITHSUNDEEP
sqloracle-database
akirekadu
akirekadu

Reputation: 2347

Oracle SQL: Return a string based on which column has the highest value

The tabled I am presented with looks similar to this

CREATE TABLE user_status (
  user_id NUMBER(10,0) PRIMARY KEY,  
  applied TIMESTAMP,
  joined TIMESTAMP,
  last_attended TIMESTAMP,
  quit TIMESTAMP
);

The database is Oracle 11g.

What SQL query could use if I would like to return APPLIED, JOINED, ACTIVE or INACTIVE based on whether applied, joined, last_attended or quit is the latest and the respective date.

It is also acceptable if I can get the name of the column (instead of APPLIED, JOINED, ACTIVE or INACTIVE) that has the latest date, if that greatly simplifies the query.

Sample rows: 

1 | 28-JUL-15 03.37.07 PM | 29-JUL-15 03.37.07 PM | 30-JUL-15 03.37.07 PM | (null)
2 | 18-JUL-15 03.55.24 PM | 19-JUL-15 03.55.24 PM | 20-JUL-15 03.55.24 PM | 31-JUL-15 03.55.24 PM

Expected result:

1 | ACTIVE | 30-JUL-15 03.37.07 PM
2 | INACTIVE | 31-JUL-15 03.55.24 PM

Update

Here's the solution I have based on Gordon Linoff's answer:

select (case greatest(coalesce(applied, to_date('0001-01-01', 'YYYY-MM-DD')), 
                      coalesce(joined, to_date('0001-01-01', 'YYYY-MM-DD')),
                      coalesce(last_attended, to_date('0001-01-01', 'YYYY-MM-DD')),
                      coalesce(quit, to_date('0001-01-01', 'YYYY-MM-DD')))
             when applied 
             then 'APPLIED'
             when joined
             then 'JOINED'
             when last_attended
             then 'INACTIVE'
             when quit
             then 'QUIT'
             else null
        end) as status, greatest(coalesce(applied, to_date('0001-01-01', 'YYYY-MM-DD')), 
                      coalesce(joined, to_date('0001-01-01', 'YYYY-MM-DD')),
                      coalesce(last_attended, to_date('0001-01-01', 'YYYY-MM-DD')),
                      coalesce(quit, to_date('0001-01-01', 'YYYY-MM-DD'))) as status_date
from user_status;

Upvotes: 2

Views: 158

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

If all have values, you can do:

select us.*
       (case when applied = greatest(applied, joined, last_attended, quit)
             then 'Applied'
             when joined = greatest(applied, joined, last_attended, quit)
             then 'Joined'
             when last_attended = greatest(applied, joined, last_attended, quit)
             then 'Last Attended'
             else 'Quit'
        end)
from user_status us

If these could have NULL values, then you need more complex logic, because greatest() returns NULL if any argument is NULL. Assuming all are in the past, then something like this:

select (case greatest(coalesce(applied, date '0000-01-01'), 
                      coalesce(joined, date '0000-01-01'),
                      coalesce(last_attended, date '0000-01-01'),
                      coalesce(quit, date '0000-01-01') )
             when applied 
             then 'Applied'
             when joined
             then 'Joined'
             when last_attended
             then 'Last Attended'
             else 'Quit'
        end)
from . . .

Upvotes: 3

Shannon Severance
Shannon Severance

Reputation: 18410

select user_id, case greatest(applied, joined, last_attended, quit)
    when applied then 'APPLIED'
    when joined then 'JOINED'
    when last_attended then 'ACTIVE'
    when quit then 'INACTIVE' end
from user_status

In the event of a tie between fields, the first field in the case expression wins.

Upvotes: 1

Ditto
Ditto

Reputation: 3344

I did it slightly different than others .. the advantage of this method is I'm also retrieving the data with it (if needed) .. ;)

  insert into user_status values ( 123, sysdate-10, sysdate-9, sysdate-8, sysdate-7 );
  insert into user_status values ( 234, sysdate-6, sysdate-1, sysdate-11, sysdate-16 );
  insert into user_status values ( 345, sysdate-5, sysdate-2, sysdate-12, sysdate-15 );
  insert into user_status values ( 456, sysdate-4, sysdate-3, sysdate-13, sysdate-14 );

  commit;

  with w_union as ( 
        select applied  dt, 'APPLIED' col, user_id
          from user_status
         union all
        select joined   dt, 'JOINED' col, user_id
          from user_status
         union all
        select last_attended  dt, 'LAST_ATTENDED' col, user_id
          from user_status
         union all
        select quit  dt, 'QUIT' col, user_id
          from user_status
     ),
     w_sub as (
        select  dt, col, user_id,
                row_number() over (order by dt desc) rnum
          from w_union
        )
  select dt, col, user_id
    from w_sub
   where rnum = 1
  /


  DT                             COL              USER_ID
  ------------------------------ ------------- ----------
  30-JUL-15 01.33.12.000000 PM   JOINED               234

Upvotes: 0

Hogan
Hogan

Reputation: 70523

Does this help?

SELECT 'APPLIED', MAX(applied) as val from user_status
  UNION ALL
SELECT 'JOINED', MAX(joined) as val  from user_status
  UNION ALL
SELECT 'ACTIVE', MAX(last_attended) as val  from user_status
  UNION ALL
SELECT 'INACTIVE', MAX(quit) as val from user_status

for example

you probably want to restrict this to a particular user in the innermost query...

SELECT what 
FROM (
  SELECT what 
  FROM ( 
    SELECT 'APPLIED' as what, MAX(applied) as val from user_status
    UNION ALL
    SELECT 'JOINED' as what, MAX(joined) as val  from user_status
    UNION ALL
    SELECT 'ACTIVE' as what, MAX(last_attended) as val  from user_status
    UNION ALL
    SELECT 'INACTIVE' as what, MAX(quit) as val from user_status
  ) sub
  ORDER BY val DESC  
) sub2
WHERE rownum = 1

Upvotes: 0

Related Questions