SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 TOP 1 for each employee
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sigmas
Posting Yak Master

Belarus
172 Posts

Posted - 07/19/2013 :  08:47:49  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3326 Posts

Posted - 07/19/2013 :  10:41:01  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4361 Posts

Posted - 07/19/2013 :  11:26:05  Show Profile  Reply with Quote
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.

Edited by - Lamprey on 07/19/2013 11:35:45
Go to Top of Page

sigmas
Posting Yak Master

Belarus
172 Posts

Posted - 07/19/2013 :  13:43:35  Show Profile  Reply with Quote
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

India
22713 Posts

Posted - 07/23/2013 :  08:46:18  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1600 Posts

Posted - 07/23/2013 :  16:47:40  Show Profile  Reply with Quote
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

Edited by - Bustaz Kool on 07/23/2013 16:49:42
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 07/23/2013 :  19:07:52  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000