David Tunnell David Tunnell -4 years ago 45
SQL Question

Select max date rows for unique key

I have a select statement.

SELECT x.ndc_id
,z.attr_val AS trade_name
,x.quote_price
,x.eff_dt FROM contract_ndc_brg x
LEFT JOIN ndc_attr AS z ON z.field_id = 150
where contract_num_val = (
SELECT item_name
FROM [contract]
WHERE item_id = 184
)


enter image description here

Notice there are two rows with the same ndc_id. I want these results but only one result for each
ndc_id
which has the highest
eff_dt
.

I tried adding to the where clause:

SELECT x.ndc_id
,z.attr_val AS trade_name
,x.quote_price
,x.eff_dt FROM contract_ndc_brg x
LEFT JOIN ndc_attr AS z ON z.field_id = 150
where contract_num_val = (
SELECT item_name
FROM [contract]
WHERE item_id = 184
) and x.eff_dt = (select max(eff_dt) from contract_ndc_brg where contract_num_val = (
SELECT item_name
FROM [contract]
WHERE item_id = 184
))


I figured out the problem with this is that it is returning the max date for any row.

How can I fix what I am doing wrong?

Answer Source

ROW_NUMBER() is your friend:

with q as
(
      SELECT x.ndc_id
        ,z.attr_val AS trade_name
        ,x.quote_price
        ,x.eff_dt
        ,row_number() over (partition by nc_id order by eff_dt desc) rn
        FROM contract_ndc_brg x
        LEFT JOIN ndc_attr AS z ON z.field_id = 150
        where contract_num_val = (
            SELECT item_name
            FROM [contract]
            WHERE item_id = 184 
            )
)
select nc_id, trade_name, quote_price, eff_dt
from q
where rn = 1
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download