pape pape - 6 months ago 9
SQL Question

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

Answer

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_.