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

The answer to this starts [here](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) — Sean Lange, Oct 10 '18 at 16:25
Imaging that this question is not yours, read it and try to understand it and answer it. Can you? — Ilyes, Oct 10 '18 at 16:56
I read it, makes sense to me. I can't answer it BECAUSE I am the one asking it. — dk96m, Oct 10 '18 at 16:58
I don't think anyone can understand for example this part: _But i dont want to include the prev column in the min, but I want that column in the result_, mybe a sample data and expected results will be the best explanation here. — Ilyes, Oct 10 '18 at 17:03
Yes, because the min function looks at the entire row and if all other columns are the same then it returns the row with the min value from both, but if the other columns aren't the same, it returns the min for each unique row. So since the prev column is different in the case, it will return both rows, but I only want the row with the min value regardless if the prev column is different. — dk96m, Oct 10 '18 at 17:07
@dk96m Of course you can answer your own question here. That would be even appreciated. — πάντα ῥεῖ, Oct 10 '18 at 17:10
Read [this](https://meta.stackoverflow.com/questions/271055/tips-for-asking-a-good-structured-query-language-sql-question) follow it, and then you will get a lot of answers to your question. — Ilyes, Oct 10 '18 at 17:17
@Sami, I dont understand why the downvotes. The question seems pretty straightforward. I have details, examples, and follow question guidelines. I dont know how better to ask. — dk96m, Mar 12 '19 at 14:51

score 0 · Answer 1 · edited Jan 13 '19 at 18:19

0

I am not 100% sure what you want because it's not clear, but my guess would be something like this.

SELECT *
FROM 
    (
      SELECT EEEV_EMPL_ID, 
             EEEV_DT, 
             prev, 
             ROW_NUMBER() OVER(PARTITION BY EEEV_EMPL_ID ORDER BY EEEV_DT) theRow
      FROM CDAS.VDWHEEEV1 eeev1 
      WHERE extract(year from eeev1.eeev_dt) = (SELECT EXTRACT(YEAR FROM SYSDATE) FROM dual) 
    ) a
WHERE theRow = 1

edited Jan 13 '19 at 18:19

Ilyes

14,640
4
29
55

answered Oct 10 '18 at 17:20

Eric

3,165
1
19
25

Care to comment why the downvote? The query will do what the OP wants. – Eric Oct 10 '18 at 17:47

score 0 · Accepted Answer · answered Mar 12 '19 at 14:59

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.

Min Function select 3 columns

2 Answers2