Selecting Unique Records with the Latest Date

Question

I'm trying to get a T-SQL query to read orders from a table and display the latest order from companies over a period of time (i.e. the last 7 days). If a company placed multiple orders over this period, only the latest order will be displayed. The query I have is below and produces the results following it. The results I would like to get is the table after that.

select StoreID, OrderID, ModDate
    from Orders

    where ModDate > (getdate() - 7)
    order by StoreID desc, ModDate desc

Output from above SQL produces:

StoreID  OrderID  ModDate
=======  =======  =======================
6093     188954   2018-08-31 12:56:09.690
6093     188883   2018-08-30 13:54:50.520
6092     189215   2018-09-06 11:01:35.257
5994     189182   2018-09-05 22:20:25.907
5994     189159   2018-09-05 17:11:22.200
5994     189169   2018-09-05 17:06:16.003
5994     189163   2018-09-05 16:04:23.683
5994     189123   2018-09-05 13:13:16.523
5994     189124   2018-09-05 13:10:30.750

Output I want:

StoreID  OrderID  ModDate
=======  =======  =======================
6093     188954   2018-08-31 12:56:09.690
6092     189215   2018-09-06 11:01:35.257
5994     189182   2018-09-05 22:20:25.907

I tried using distinct hoping it would only pick the StoreID's (i.e. select distinct StoreID ...) but it didn't make a difference. What am I missing to get this query to work? In the worst case, I can use the data the way it is and have C# not display duplicated StoreID's, but I would rather have correct output from good SQL.

Thanks.

score 1 · Accepted Answer · answered Sep 06 '18 at 21:28

You can try to make row number by StoreID colunm then order by ModDate desc

then get the rn = 1 data.

CREATE TABLE T(
   StoreID int,
   OrderID int,
   ModDate datetime
);




insert into t values (6093,188954,'2018-08-31 12:56:09.690');
insert into t values (6093,188883,'2018-08-30 13:54:50.520');
insert into t values (6092,189215,'2018-09-06 11:01:35.257');
insert into t values (5994,189182,'2018-09-05 22:20:25.907');
insert into t values (5994,189159,'2018-09-05 17:11:22.200');
insert into t values (5994,189169,'2018-09-05 17:06:16.003');
insert into t values (5994,189163,'2018-09-05 16:04:23.683');
insert into t values (5994,189123,'2018-09-05 13:13:16.523');
insert into t values (5994,189124,'2018-09-05 13:10:30.750');

Query 1:

select StoreID,OrderID,ModDate 
from (
    SELECT *,row_number() OVER(PARTITION BY StoreID  order by ModDate desc) rn
    FROM T
)t1
where rn = 1

Results:

| StoreID | OrderID |                  ModDate |
|---------|---------|--------------------------|
|    5994 |  189182 | 2018-09-05T22:20:25.907Z |
|    6092 |  189215 | 2018-09-06T11:01:35.257Z |
|    6093 |  188954 |  2018-08-31T12:56:09.69Z |

Thanks D-Shih ... works perfectly. :) – UltraJ Sep 06 '18 at 21:42 — UltraJ, Sep 06 '18 at 21:42

Selecting Unique Records with the Latest Date

1 Answers1