0

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: enter image description here

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: enter image description here

PiotrekM
  • 27
  • 6

1 Answers1

1

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
George Menoutis
  • 6,894
  • 3
  • 19
  • 43
  • Great, thank you, that's exactly what I was looking for! CTE is something new for me, so I'll have to explore the topic. Thanks once again. – PiotrekM Jun 07 '22 at 11:51
  • The CTE wasn't that integral to the answer, I just used it to isolate your query since it had `dinstinct` which would complicate things. – George Menoutis Jun 07 '22 at 12:10