July 19, 2013 at 7:11 am
I need to select last order for each employees for homework
I use northwind database for testing
I can solve it by correlated subquery but the professor said me it is not optimized.
Can someone to solve it in efficient way?
my solution
select orderid, customerid, employeeid, orderdate
from orders as o1
where orderdate = (select max(orderdate) from orders as o2 where o1.employeeid = o2.employeeid);
July 19, 2013 at 8:09 am
You have a common solution that is used more often than you would expect.
However, I have a question for you. Do you want a complete solution or just what you need to get to it?
You could use CTE (Common table expressions) and ROW_NUMBER(). If you need more help you can ask for it.
If you're just using those columns and the orderid is incremental and you can't change the orders' date then a simple MAX() on orderdate and orderid will work along with a group by.
July 19, 2013 at 8:13 am
You could use CTE (Common table expressions) and ROW_NUMBER().
I could use Derived Table instead CTE.
July 19, 2013 at 8:26 am
Yes you can. I just have personal preference for CTEs. 😉
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply