How to find the date of the last item sold sql server

Question

Hi Guys I am having a bit of trouble writing the most efficient and optimized query for this question:

Find the order ID and date of the last discontinued item sold.

I have my code below as well as the metadata for the tables. I am not sure if my code will produce the correct output because I have no way of testing it and I am not sure if my code will be the best way to complete this query. Any advice would help.

Select 
orders.orderid,
Max(orders.orderdate)
from orders
inner join order_details on orders.orderid = order_details.orderid
inner join products on order_details.productid = products.productid
where discontinued = 1 
group by orders.orderid  ```

Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — Thom A, Apr 15 '21 at 12:29
Hi no it does not. I also need to convert the datetime into date and find maxdate. — klajdiziaj930, Apr 15 '21 at 12:55
If you also need to convert a `datetime` to a `date` that's just `CONVERT`; but that isn't the "meat" of what you are asking here. — Thom A, Apr 15 '21 at 12:58

score 0 · Answer 1 · answered Apr 15 '21 at 12:56

0

Using row_number is the easiest way:

select orderID,OrderDate from (
  select o.orderID,o.OrderDate,rn = row_number() over (order by orderdate desc)
  from products p
    join orderDetails od on od.productID=p.productID
    join orders o on o.orderID=od.orderID
  where p.discontinued = 1) sub
where sub.rn = 1

answered Apr 15 '21 at 12:56

KeithL

5,348
3
19
25

Is this more efficient than selecting top 1 in the select statement ? – klajdiziaj930 Apr 15 '21 at 13:07
FYI, this is doing exactly what you said your question *isn't* a duplicate of, @klajdiziaj930 ... – Thom A Apr 15 '21 at 13:08
I can't speak to efficiency without testing, but this IS the way to solve your problem. – KeithL Apr 15 '21 at 13:15
if efficiency is your goal then make sure you have indexes on your foreign key [orderID] and on [orderdate] – KeithL Apr 15 '21 at 13:22

Stu · Answer 2 · 2021-04-15T13:53:58.413

your query is pretty much already what you want, the fastest way is to simply order by the required column and select top 1

select top (1) o.orderid, o.orderdate
from orders o
join order_details od on od.orderid = o.orderid
join products p on p.productid = od.productid
where p.discontinued = 1 
order by o.orderdate desc

This will be more performant than using a window function to number all the rows before selecting row one.

I have a very similar arrangement of tables with the ubiquitous orders/orderitems/products arrangement including a similar deleted flag for products so it's easy to test both side by side, this query is a bit more performant than using the row_number equivalent, using a table of 13.5m orders and 6m products. Execution times for both were sub-second but this query was slightly faster.

How to find the date of the last item sold sql server

2 Answers2