Based on your expected output, you don't want "distinct" records; you want
the last record for each order ID.
WITH cte As
(
SELECT
order_id,
hierarchyid,
itemname,
Createddate,
ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY Createddate DESC) As RN
FROM
orders
)
SELECT
order_id,
hierarchyid,
itemname,
Createddate
FROM
cte
WHERE
RN = 1
;
ROW_NUMBER (Transact-SQL) - SQL Server | Microsoft Learn[
^]
NB: If two orders have the same
Createddate
, then this will pick one at random. You should either add an additional "tie-breaking" sort condition to the
ROW_NUMBER
; or, if you want to return all rows for the latest order, switch to using
the RANK
function[
^] instead.