CODEWITHSUNDEEP
sqloracle-databasewindow-functions
John
John

Reputation: 299

Window Functions

I want to add a window functions.

Take the min date when visit = Y and end as Associd.

TableA

ID    Date      AssocId  Visit
 1     1/1/17    10101     Y
 1     1/2/17    10102     Y

End Results.

ID    Date    AssocId
 1    1/1/17    10101

SQL > This gives me the min date but I need the AssocId associated to that date.

 SELECT MIN(CASE WHEN A.VISIT = 'Y'
 THEN A.DATE END) OVER (PARTITION BY ID) 
 AS MIN_DT,

Upvotes: 1

Views: 1860

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You can use FIRST_VALUE():

 SELECT MIN(CASE WHEN A.VISIT = 'Y' THEN A.DATE END) OVER (PARTITION BY ID)  AS MIN_DT,
        FIRST_VALUE(CASE WHEN A.VISIT = 'Y' THEN A.ASSOCID END) KEEP (DENSE_RANK FIRST OVER (PARTITION BY ID ORDER BY A.VISIT DESC, A.DATE ASC),

Note that this is a little tricky with conditional operations. I would be more inclined to use a subquery to nest the query operations. The outer expression would be:

SELECT MAX(CASE WHEN Date = MIN_DT THEN ASSOCID END) OVER (PARTITION BY ID)

If you wanted this per ID, I would suggest:

select id, min(date),
       first_value(associd) over (partition by id order by date)
from t
where visit = 'Y'
group by id;

That is, use aggregation functions.

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You seems want :

select t.*
from table t
where visit = 'Y' and
      date= (select min(t1.date) from table t1 where t1.id = t.id);

Upvotes: 0

Related Questions