SQL select case group by
<p>I have a table 'LIST_USERS'.</p>

<p>Table Description -</p>

<pre><code>USER_ID       NUMBER(8)
LOGIN_ID      VARCHAR2(8)
CREATE_DATE   TIMESTAMP(6)
LOGIN_DATE    TIMESTAMP(6)
</code></pre>

<p>Table data -</p>

<pre><code>USER_ID     LOGIN_ID    CREATE_DATE     LOGIN_DATE
---------------------------------------------------
101         test1       04/24/2016      null
102         test1       04/24/2016      04/29/2016
103         test2       04/25/2016      null
104         test2       04/26/2016      null
105         test3       04/27/2016      04/28/2016
106         test3       04/27/2016      04/29/2016
107         test4       04/28/2016      04/29/2016
987         test5       04/29/2016      null
109         test5       04/29/2016      null
108         test5       04/29/2016      04/29/2016
</code></pre>

<p>Condition  - I need to fetch USER_ID, and LOGIN_ID from 'LIST_USERS' table based of max LOGIN_DATE. If LOGIN_DATE is null, I need to get the record based on max CREATE_DATE.</p>

<p>I need to get the below result - </p>

<pre><code>USER_ID     LOGIN_ID
---------------------
102         test1   
104         test2
106         test3
107         test4
108         test5
</code></pre>

<p>I am using the below query. But it will give me only LOGIN_ID, and 'Login_Or_Create_Date' but I need USER_ID, and LOGIN_ID. Is there way I can get USER_ID as well as in the result shown above?</p>

<pre><code>select LOGIN_ID,
       (case when max(LOGIN_DATE) is null then max(CREATE_DATE)
             else max(LOGIN_DATE) end) as Login_Or_Create_Date
from   LIST_USERS;
</code></pre>