SELECT C.CustomerID, O.SalesOrderID, O.TotalDue
FROM AdventureWorks.Sales.Customer AS C
INNER JOIN AdventureWorks.Sales.SalesOrderHeader AS O
  ON C.CustomerID = O.CustomerID
WHERE ROW_NUMBER() OVER (
  PARTITION BY C.CustomerID ORDER BY O.TotalDue DESC) <= 3
ORDER BY C.CustomerID ASC, O.TotalDue DESC

quote:

---

Originally posted by vrapp
You could join a table to a table-valued function in sql2k with regular join.

---

quote:

---

Originally posted by AskSQLTeam

My interest in writing this article was started by an MSDN article titled <a href="http://msdn.microsoft.com/SQL/default.aspx?pull=/library/en-us/dnreal/html/realworld03112005.asp">SQL Server 2005: The CLR Enters the Relational Stage</a>. The article shows how to write a function that returns the top three countries per category. That's always been something that was difficult to do in SQL so I was curious about the approach. The article started out well but I was very unhappy by the end. It's just soooo much easier to do this in SQL Server 2005 using the new CROSS APPLY clause in Transact-SQL. So I'm going to write a query to return the top 3 orders for each customer and I'm going to do it in about 10 lines of SQL.<P>Article <a href="/item.asp?ItemID=21502">Link</a>.

---

quote:

---

Originally posted by Kristen

Unless I've overlooked some subtle detail in your example I reckon you can do that in SQL 2000, probably SQL 7 even, rather than specifically only later versions.

---

quote:

---

Originally posted by robajz


select t1.id, t2sq.id
from t1
left join (
select top 3 *
from t2
order by t2.id desc
) t2sq
on t2sq.t1_id = t1.id

---

select t1.id, t2.id
 from t1
 left join t2
	on t2.t1_id = t1.id
 where t2.id is null or t2.id in
   (select top 3 id
		from  t2
		where t1_id = t1.id
		order by t1.id desc)

quote:

---

Originally posted by Kristen

I substituted Table and Column names in your first query and ran it against SQL 2000 here - ran fine - so I don;t think the syntax will be a problem.

Your second style should be fine too (haven't looked at the logic closely though)

---