Nasty SubQuery

Database
Enthusiast

Nasty SubQuery

Please help. We wish to return 8 rows from the final piece of sequel below.

SELECT a.*
FROM tmp_risk_detail a
WHERE a.Effective_date_id =
(SELECT MAX(c.effective_date_id)
FROM tmp_risk_detail c
WHERE a.policy_id = c.policy_id
AND a.Po_risk_detail_id = c.Po_risk_detail_id
AND EXTRACT(YEAR FROM a.effective_date_id) = EXTRACT(YEAR FROM c.effective_date_id)
and a.policy_id = 3857622
)

-- 1st statement returns 16 rows for the policy id
SELECT a.*
FROM tmp_risk_detail a
WHERE a.policy_id = 3857622

-- Following subquery returns 05/12/2006
(SELECT MAX(c.effective_date_id)
FROM tmp_risk_detail c, tmp_risk_detail a
WHERE a.policy_id = c.policy_id
AND a.Po_risk_detail_id = c.Po_risk_detail_id
AND EXTRACT(YEAR FROM a.effective_date_id) = EXTRACT(YEAR FROM c.effective_date_id)
and a.policy_id = 3857622
)

-- use the result from the subquery and we return 8 rows (all with Effective_date_id = '2006-12-05' )
SELECT a.*
FROM tmp_risk_detail a
WHERE a.Effective_date_id = '2006-12-05'

-- Use the subquery in this join and we return 16 rows some of which are <> '2006-12-05'
SELECT a.*
FROM tmp_risk_detail a
WHERE a.Effective_date_id =
(SELECT MAX(c.effective_date_id)
FROM tmp_risk_detail c
WHERE a.policy_id = c.policy_id
AND a.Po_risk_detail_id = c.Po_risk_detail_id
AND EXTRACT(YEAR FROM a.effective_date_id) = EXTRACT(YEAR FROM c.effective_date_id)
and a.policy_id = 3857622
)

sample data
policy_id Po_risk_detail_id product_id po_risk_desc effective_date_id Tr_seq_id
3857622 11898108 1275040 GEI/COM/00072271 05/12/2005 202
3857622 11898110 1384804 GEI/COM/00072271 05/12/2006 301
3857622 11898111 1439102 GEI/COM/00072271 05/12/2005 202
3857622 11898110 1384804 GEI/COM/00072271 05/12/2006 301
3857622 11898111 1439102 GEI/COM/00072271 05/12/2006 301
3857622 11898108 1275040 GEI/COM/00072271 05/12/2006 301
3857622 11898111 1439102 GEI/COM/00072271 05/12/2005 101
3857622 11898108 1275040 GEI/COM/00072271 05/12/2005 101
3857622 11898110 1384804 GEI/COM/00072271 05/12/2005 202
3857622 11898109 1230461 GEI/COM/00072271 05/12/2006 301
3857622 11898111 1439102 GEI/COM/00072271 05/12/2006 301
3857622 11898108 1275040 GEI/COM/00072271 05/12/2006 301
3857622 11898109 1230461 GEI/COM/00072271 05/12/2005 101
3857622 11898109 1230461 GEI/COM/00072271 05/12/2006 301
3857622 11898109 1230461 GEI/COM/00072271 05/12/2005 202
3857622 11898110 1384804 GEI/COM/00072271 05/12/2005 101
2 REPLIES
Enthusiast

Re: Nasty SubQuery

If you are trying to get the row with the most recent effective_date_id for each policy_id and Po_risk_detail_id, you can do this:

SELECT *
FROM tmp_risk_detail
WHERE policy_id = 387622
QUALIFY SUM(1) OVER (PARTITION BY policy_id, po_risk_detail_id
ORDER BY effective_date_id desc
ROWS UNBOUNDED PRECEDING) = 1;

I'm not sure what you're trying to do with the "EXTRACT(YEAR.." part.

Enthusiast

Re: Nasty SubQuery

Thanks..

Have it sorted now.