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

You would use ROW_NUMBER. https://learn.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql — Sean Lange, Apr 10 '17 at 20:06

score 1 · Accepted Answer · answered Apr 10 '17 at 20:07

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)

answered Apr 10 '17 at 20:07

John Cappelletti

79,615
7
44
66

coenni · Answer 2 · 2017-04-10T20:12:51.407

0

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)

edited Apr 10 '17 at 20:12

answered Apr 10 '17 at 20:09

coenni

437
4
14

1

LIMIT 1 isn't sql server syntax. – S3S Apr 10 '17 at 20:12
i updated it removing limit and adding customerid control – coenni Apr 10 '17 at 20:13

score 0 · Answer 3 · answered Apr 10 '17 at 20:09

0

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
)

answered Apr 10 '17 at 20:09

jerry

1,817
1
11
6

You would want an index on [Customer Id] and DateSubmitted for this to run efficiently. – jerry Apr 10 '17 at 20:13

score 0 · Answer 4 · edited Apr 11 '17 at 05:12

0

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)

edited Apr 11 '17 at 05:12

slavoo

5,798
64
37
39

answered Apr 10 '17 at 20:17

Carolina Gil

1
1

Distinct and latest submissions

4 Answers4