Distinct and latest submissions

Question

I have a table where each customer can submit many requests, but only latest requests would be entertained.

For example, Customer1 may have only submitted 1 request and CustomerX may have submitted 10 requests. So when I pull the report it would bring the 1 request by customer1 and the 10th request by CustomerX.

How can I do that?

Customer Id, FoodSelection1, FoodSelection2, DateSubmitted

Answer 1

You can use the WITH TIES clause in concert with Row_Number()

Select Top 1 with ties *
 From YourTable
 Order By Row_Number() over (Partition By CustomerID Order by DateSubmitted Desc)

Answer 2

SELECT m.CustomerId, m.FoodSelection1, m.FoodSelection2, m.DateSubmitted FROM tblpm m 
  WHERE m.DateSubmitted =(SELECT max(n.DateSubmitted ) FROM tblpm n 
                       where n.CustomerId=m.CustomerIdORDER)

Answer 3

select * from MyTable T1 
where not exists --exclude records where 
(
select 1 from MyTable T2 
where T1.[Customer Id]=T2.[Customer Id] --there is matching rcd for customer
and T1.DateSubmitted<T2.DateSubmitted --a newer one exists
)

Answer 4

select 
    t.Customer, 
    t.id, 
    t.FoodSelection1, 
    t.FoodSelection2, 
    t.DateSubmitted 
from MyTable T1 as t 
where t.datesubmited in 
     (select max(r.datesubmited) 
      from table 1 as r
      where t.idCustomer = r.idCustomer
      group by r.idcustomer)