Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
sigmas
Posting Yak Master
172 Posts |
Posted - 2013-07-19 : 08:47:49
|
I need to select last order for each employees for homeworkI use northwind database for testingI can solve it by correlated subquery but the professor said me it is not optimized. Can someone to solve it in efficient way?my solutionselect orderid, customerid, employeeid, orderdatefrom orders as o1where orderdate = (select max(orderdate) from orders as o2 where o1.employeeid = o2.employeeid); |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-07-19 : 10:41:01
|
You can use the ROW_NUMBER() function (or RANK() function if you want to get ties). Generate a CTE or subquery with row_number partitioned by employee and ordered by order date desc then in your outer select only those where the row number = 1.Another similar way is to use cross apply. In your cross apply, you would select the TOP (1) (or TOP (1) WITH TIES) where the employee id in the inner query equals the employee id of the outer query and order by order date desc. |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-07-19 : 11:26:05
|
I'm guess your professor is saying that corrilated sub-queries are not efficient. Which, in genreal, is true. But, I think what he/she is realy saying to you is to think more in sets. Here is way to do it using a join to a derived table:select orders.orderid, orders.customerid, orders.employeeid, emporder.orderdatefrom orders inner join ( select employeeid, max(orderdate) as orderdate from orders group by employeeid ) as emporder on orders.employeeid = emporder.employeeid and orders.orderdate = emporder.orderdate Note: that if there is more than one order per employee on that smae date you might get duplicate rows, so you might need to do additioanl (DISTINCT) filtering.EDIT: I think I misread your requriement. So, my solution probalby doesn't work for you. |
|
|
sigmas
Posting Yak Master
172 Posts |
Posted - 2013-07-19 : 13:43:35
|
quote: Originally posted by Lamprey I'm guess your professor is saying that corrilated sub-queries are not efficient. Which, in genreal, is true. But, I think what he/she is realy saying to you is to think more in sets. Here is way to do it using a join to a derived table:select orders.orderid, orders.customerid, orders.employeeid, emporder.orderdatefrom orders inner join ( select employeeid, max(orderdate) as orderdate from orders group by employeeid ) as emporder on orders.employeeid = emporder.employeeid and orders.orderdate = emporder.orderdate Note: that if there is more than one order per employee on that smae date you might get duplicate rows, so you might need to do additioanl (DISTINCT) filtering.EDIT: I think I misread your requriement. So, my solution probalby doesn't work for you.
Your query is correct. I used this too:http://en.wikipedia.org/wiki/Correlated_subqueryWhat is your browser? the Firefox check spelling.You have 5 typos. |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2013-07-23 : 16:47:40
|
In re: "You have 5 typos." Do you look EVERY gift horse in the mouth?Also, he had a hat.=================================================The cure for anything is salt water -- sweat, tears, or the sea. -Isak Dinesen |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-07-23 : 19:07:52
|
[code]WITH cteSource(OrderID, CustomerID, EmployeeID, OrderDate, rnkAS ( SELECT OrderID, CustomerID, EmployeeID, OrderDate, RANK() OVER (PARTITION BY EmployeeID ORDER BY OrderDate DESC) AS rnk FROM dbo.Orders)SELECT OrderID, CustomerID, EmployeeID, OrderDateFROM cteSourceWHERE rnk = 1;[/code] Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
|
|
|
|
|