SQL filter query result

June 7, 2022

I have a problem to create query.
Now I have query like this:

SELECT DISTINCT sad.item_id, sad.attrb_code, sad.sub_item_id, sad.attrb_type, sad.description, sad.effective_date, sad.creation_date, sad.last_update_datetime, sad.last_user_id 
FROM table1 AS sad 
WHERE NOT EXISTS 
( 
    SELECT 1 FROM table2 AS saa 
    WHERE sad.attrb_code = saa.attrb_code AND sad.item_id = saa.item_id AND saa.attrb_flag = 'N' 
) 
AND sad.attrb_code IN ('VOICE', 'SMS2D', 'MMS2D', 'TRANS' )
AND sad.item_id = '???' ;

and result looks like this:

Now I would like to filter results, to select only the newest one (by effective_date) for every unique attrb_code. So I would like to achive something like this:

>Solution :

Wrap your query in a CTE for clarity, then top 1 with ties and row_number:

;with original as
(
    <put your entire query here>
)
select top 1 with ties *
from original 
order by row_number() over (partition by attrb_code order by effective_date desc) asc