by using group and having query

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

SQL Server 2000 Forums

Transact-SQL (2000)

by using group and having query

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-06-07 : 11:18:10

vamshi writes "Order table (orderno,orderdate,amount,customerno,quantity)
now i want each customer last order.
i wrote
select * from [order] o1 where o1.orderdate=(select max(o2.orderdate) from [order] o2 where o1.customerno=o2.customerno)

but it is taking 8 to 10 secs ,order table have 34000 records
but clients 700.
so i want execution time is less by using group"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-07 : 12:14:09

That's because your subquery runs for each row in order table.

Use this instead.

SELECT		*
FROM		Order
INNER JOIN 	(
			SELECT CustomerNo, MAX(OrderDate) MaxDate
			FROM Order
			GROUP BY CustomerNo
		) z ON z.CustomerNo = Order.CustomerNo AND z.MaxDate = Order.OrderDate

Let me know if there were a speed difference.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources