Click here to Skip to main content
15,903,012 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I have a table with orders and need to join unique records from history table could you please assist on how to get unique records by create date

order_id  itemname  hierarchyid  Createddate
1         Keyboard  4000         12/11/2017
2         Mouse     3000         12/11/2016
3         Monitor   1200         12/11/2015
4         Keyboard  4000         12/11/2014
5         Mousepad  3000         12/11/2013
1         Monitor   1200         12/11/2012
2         Keyboard  4000         12/11/2011
5         Mousepad  3000         12/11/2010

;with cte as (select distinct order_id ,hierarchyid, itemname, Createddate from orders order by createdate)
Select * from cte


What I have tried:

I have tried to get unique records with the below cte but not able to distinct records
order_id  itemname  hierarchyid  Createddate
1         Keyboard  4000         12/11/2017
2         Mouse     3000         12/11/2016
3         Monitor   1200         12/11/2015
4         Keyboard  4000         12/11/2014
5         Mousepad  3000         12/11/2013
1         Monitor   1200         12/11/2012
2         Keyboard  4000         12/11/2011
5         Mousepad  3000         12/11/2010


;with cte as (select distinct order_id ,hierarchyid, itemname, Createddate from orders order by createdate)
Select * from cte


Expected result should be like below
order_id  itemname  hierarchyid  Createddate
1         Keyboard  4000         12/11/2017
2         Mouse     3000         12/11/2016
3         Monitor   1200         12/11/2015
4         Keyboard  4000         12/11/2014
5         Mousepad  3000         12/11/2013
Posted
Updated 25-Mar-24 8:25am
v2
Comments
PIEBALDconsult 25-Mar-24 14:28pm    
0) I think it's doing exactly what you told it to do.
1) Avoid using DISTINCT.
2) Don't put the ORDER BY in the CTE.
Ganesh Kumar 2024 25-Mar-24 14:41pm    
here not to get duplicate records which are repeated and need to get only last update single records only

You should Group By the first three columns in your results and use Max() of your last column. That should get you what I think you want.

Select order_id, itemname, hierarchyid, Max(Createddate) as LastOrderDate
From orders
Group By order_id, itemname, hierarchyid
Order By Createddate desc
 
Share this answer
 
Based on your expected output, you don't want "distinct" records; you want the last record for each order ID.
SQL
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.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900