Min Function select 3 columns

Question

I have an sql statement where I want to select the min of a date:

SELECT EEEV_EMPL_ID, MIN(EEEV_DT), prev

But i dont want to include the prev column in the min, but I want that column in the result. So say I have

1   3/5/2018    UB3 
1   5/28/2018   4A

where the first column is the employee id, second is date, third is prev. If i do this, both rows will be returned because the prev column is different. I want to return just the row with the min date, regardless of the prev column being different. How do I do that?

This is the full statement:

SELECT EEEV_EMPL_ID, EEEV_DT, prev
FROM (
       SELECT EEEV_EMPL_ID, EEEV_DT, EEEV_CCTR_ID,LAG(EEEV_CCTR_ID)          
              OVER(ORDER BY EEEV_EMPL_ID DESC, EEEV_DT DESC) AS prev      
       FROM CDAS.VDWHEEEV1 eeev1 
       WHERE extract(year from eeev1.eeev_dt) = 
             (select extract(year from sysdate) from dual) 
       ORDER BY EEEV_EMPL_ID
     ) x
GROUP BY EEEV_EMPL_ID, prev
ORDER BY EEEV_EMPL_ID

This is an oracle query, but I will be using it in an OPENQUERY in sql server.

Update:

OK, so this is not clear to some people, let me try this:

00012662    3/5/18     2C
00012662    5/28/18    UB3
00037465    3/19/18    PA
00037465    5/28/18    UB
...

I want these two rows returned. For each employee I want the min date value row. But because the third column is different, the min date value will return every row. Dont know how more simply to ask it.

00012662    3/5/18     2C
00037465    3/19/18    PA

Answer 1

The answered lies in the LAG function.

SELECT
      EEEV_EMPL_ID, 
      EEEV_DT, 
      EEEV_CCTR_ID, 
      LAG(EEEV_CCTR_ID, 1, ' ') OVER(PARTITION BY EEEV_EMPL_ID ORDER BY EEEV_EMPL_ID    DESC, EEEV_END_DT ASC) AS prev
FROM    
      CDAS.VDWHEEEV1 eeev1

With the lag function, you are able to partition the data so only base it on the column you wish. The final query is below:

SELECT 
        EEEV_EMPL_ID, 
        EEEV_DT, 
        EEEV_CCTR_ID, 
        prev
FROM
        (
            SELECT 
                    EEEV_EMPL_ID, 
                    EEEV_DT, 
                    MIN(EEEV_DT) OVER(PARTITION BY EEEV_EMPL_ID) AS EEEV_DT1, 
                    EEEV_CCTR_ID, 
                    prev
            FROM 
                (
                        SELECT
                                EEEV_EMPL_ID, 
                                EEEV_DT, 
                                EEEV_CCTR_ID, 
                                LAG(EEEV_CCTR_ID, 1, ' ') OVER(PARTITION BY EEEV_EMPL_ID ORDER BY EEEV_EMPL_ID DESC, EEEV_END_DT ASC) AS prev
                        FROM    
                                CDAS.VDWHEEEV1 eeev1
                        ORDER BY 
                                EEEV_EMPL_ID
                ) x
            WHERE 
                    extract(year from eeev_dt) = (select extract(year from sysdate) from dual) AND
                    EEEV_CCTR_ID IN (@cctrlst)
            ORDER BY
                    EEEV_EMPL_ID
        )
WHERE EEEV_DT = EEEV_DT1
ORDER BY
        EEEV_EMPL_ID

This query provided the solution I needed to obtain the results I was looking for in the original post.