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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 TOP 1 for each employee

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 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);



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.
Go to Top of Page

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.orderdate
from
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.
Go to Top of Page

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.orderdate
from
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_subquery

What is your browser? the Firefox check spelling.
You have 5 typos.
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-07-23 : 08:46:18
Also refer http://beyondrelational.com/modules/2/blogs/70/posts/10845/return-top-n-rows.aspx

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-23 : 19:07:52
[code]WITH cteSource(OrderID, CustomerID, EmployeeID, OrderDate, rnk
AS (
SELECT OrderID,
CustomerID,
EmployeeID,
OrderDate,
RANK() OVER (PARTITION BY EmployeeID ORDER BY OrderDate DESC) AS rnk
FROM dbo.Orders
)
SELECT OrderID,
CustomerID,
EmployeeID,
OrderDate
FROM cteSource
WHERE rnk = 1;[/code]


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page
   

- Advertisement -