Identify the max date

I am attempting to identify the max(date) of each record, but am having a hard time getting it right.

Here is what I have:

Employee #  |   Role  |  Status  |  Start Date
23432        Associate  Not Active 04/23/2011    
23432        Manager    Active     11/2/2012
54332        Analyst    Resigned   10/15/2015
12311        Help Desk  Not Active 05/12/2014
12311        Analyst    Not Active 06/11/2015
12311        Supervisor Active     07/12/2016

Code:

SELECT "EMPLOYEE #", "ROLE", "STATUS", "START DATE" 
(CASE 
   WHEN "ROLE START" = MAX("START DATE")
   THEN 1
   ELSE 0
END) "MAX" FROM (
SELECT DISTINCT EMPLOYEE "EMPLOYEE #",
LR.DESCRIPTION "ROLE", 
R.ROLE_STATUS "STATUS",
ROLE_START_DATE "START DATE"
FROM EMPLOYEES E
JOIN ROLES R ON E.EMPLOYEE_ID = R.EMPLOYEE_ID
JOIN LU_ROLES LR ON R.ROLE_ID = LR.ROLE_ID
WHERE ROLE_START_DATE >= DATE '2017-12-03'
ORDER BY 1)
GROUP BY "EMPLOYEE #", "ROLE", "STATUS", "START DATE"

What I would like to have is

Employee #  |   Role  |  Status  |  Start Date | Max
23432        Associate  Not Active 04/23/2011    0
23432        Manager    Active     11/2/2012     1
54332        Analyst    Resigned   10/15/2015    1
12311        Help Desk  Not Active 05/12/2014    0
12311        Analyst    Not Active 06/11/2015    0
12311        Supervisor Active     07/12/2016    1

Any help you all could provide would be much appreciated.

Thanks in advance!

Solution

You can try to write CASE WHEN with ROW_NUMBER windows function.

SELECT t.*,
       (CASE WHEN ROW_NUMBER() OVER(PARTITION BY "Employee #" ORDER BY "Start Date" DESC) = 1 THEN 1 ELSE 0 END) 'Max'
FROM yourTable t

Or use CASE WHEN with RANK windows function.

SELECT t.*,
       (CASE WHEN RANK() OVER(PARTITION BY "Employee #" ORDER BY "Start Date" DESC) = 1 THEN 1 ELSE 0 END) 'Max'
FROM yourTable t

SQLFIDDLE

[Results]:

| EMPLOYEE |       ROLE |      STATUS |            STARTDATE | MAX |
|----------|------------|-------------|----------------------|-----|
|    12311 | Supervisor |  Active     | 2016-07-12T00:00:00Z |   1 |
|    12311 | Analyst    |  Not Active | 2015-06-11T00:00:00Z |   0 |
|    12311 | Help Desk  |  Not Active | 2014-05-12T00:00:00Z |   0 |
|    23432 | Manager    |  Active     | 2012-11-02T00:00:00Z |   1 |
|    23432 |  Associate |  Not Active | 2011-04-23T00:00:00Z |   0 |
|    54332 | Analyst    |  Resigned   | 2015-10-15T00:00:00Z |   1 |