CODEWITHSUNDEEP
sql-serverstored-proceduresdistinct
A.Goutam
A.Goutam

Reputation: 3494

query is not returning distinct record

Hi can you please take a look why my query is not returning distinct record. i want result with following condition OE1='SCHEDCHNG', need only recent records per orderid or ordernum means only one record per ordernum or orderid and also dropdate is null. My query is as below 

    select DISTINCT TOP 100 OE.ORDERID,OE.ID,OE.ORDERNUM,OE.OE4  from OrderExports OE
inner join (
    select ORDERNUM, max(OE4) as MaxDate
    from OrderExports
    group by ORDERNUM
) tm
 on OE.ORDERNUM = tm.ORDERNUM and OE.OE4 = tm.MaxDate
 inner join orde_ O on OE.ORDERID = O.ORDERID 
 WHERE OE1='SCHEDCHNG' AND O.DROPDATE is null

query result with duplicate record

Upvotes: 0

Views: 42

Answers (2)

Sean Lange
Sean Lange

Reputation: 33581

Pretty sparse on details here but I think you are wanting something along these lines.

with SortedResults as
(
    select OE.ORDERID
        , OE.ID
        , OE.ORDERNUM
        , OE.OE4  
        , ROW_NUMBER() over(partition by OE.ORDERID, OE.ORDERNUM order by OE.OE4 desc) as RowNum
    from OrderExports OE
    inner join 
    (
        select ORDERNUM
            , max(OE4) as MaxDate
        from OrderExports
        group by ORDERNUM
    ) tm on OE.ORDERNUM = tm.ORDERNUM and OE.OE4 = tm.MaxDate
     inner join orde_ O on OE.ORDERID = O.ORDERID 
     WHERE OE1='SCHEDCHNG' 
        AND O.DROPDATE is null 
)

select ORDERID
    , ID
    , ORDERNUM
    , OE4
from SortedResults
where RowNum = 1

Upvotes: 2

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13969

You can try using max and group by as below :

SELECT a.ID, max(a.ORDERID) as OrderID, max(a.ORDERNUM) as OrderNum,MAX(OE.OE4) as OE4 FROM
(
--your query
 ) a
 group by a.ID

Upvotes: 0

Related Questions