joins instead of subqueries - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

joins instead of subqueries

Author

Topic

kwacz23
Starting Member

44 Posts

Posted - 2014-03-19 : 09:03:38

Hi

How to change below query for JOiNS query

select * from Sales.Orders a
where a.orderid in ( select max(b.orderid) from sales.orders b
where b.custid=a.custid)

Task : Finad for every user max order ID

Regards

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2014-03-19 : 09:26:23

select custid, max(orderid) as orderid from sales.orders group by custid

Too old to Rock'n'Roll too young to die.

sqlsaga
Yak Posting Veteran

93 Posts

Posted - 2014-03-19 : 11:05:29

Use this...

;WITH CTE AS
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY CustID ORDER BY OrderID Desc) AS Rnum
FROM Sales.Orders
)
SELECT Column_Names
FROM CTE WHERE Rnum = 1

Read more about ranking functions at http://sqlsaga.com/sql-server/what-is-the-difference-between-rank-dense_rank-row_number-and-ntile-in-sql-server/

Visit www.sqlsaga.com for more t-sql snippets and BI related how to's.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources