CODEWITHSUNDEEP
sql
pape
pape

Reputation: 239

SQL - remove duplicates from query

I'm trying to create a SQL query that will not return rows that have the same No_. How can I remove the duplicates in No_ column? I want to leave only data based on last Posting Date column.

select  Item.No_, Entry.[Posting Date], Entry.[Remaining Quantity], MinMax.Maximum
FROM Item
join Entry
on Item.No_ = Entry.[Item No_]
join MinMax
 on Item.No_ = MinMax.Item No_
order by  Entry.[Remaining Quantity] desc

The output:

enter image description here

but I want the output is last Posting Date:

enter image description here

Upvotes: 0

Views: 98

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You can use a derived table containing the latest date per No_:

select  Item.No_, 
        Entry.[Posting Date], Entry.[Remaining Quantity], 
        MinMax.Maximum
FROM Item
join (
   select max([Posting Date]) as max_Date, [Item No_]
   from Entry
   group by [Item No_]
) as e on e.[Item No_] = Item.No_
join Entry on Item.No_ = Entry.[Item No_] and e.max_Date = Entry.[Posting Date]
join MinMax on Item.No_ = MinMax.Item No_
order by  Entry.[Remaining Quantity] desc

Joining to this derived table filters out records not being related to the record having the latest [Posting Date] per No_.

Upvotes: 1

Related Questions