Advertisement
Guest User

Untitled

a guest
Apr 17th, 2014
42
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.06 KB | None | 0 0
  1. SELECT CustomerID, OrderDate
  2. FROM Orders
  3. ORDER BY CustomerID;
  4.  
  5. SELECT CustomerId, OrderDate
  6. FROM Orders as ExtOrders
  7. WHERE OrderId IN (
  8. SELECT TOP 4 OrderId
  9. FROM Orders
  10. WHERE CustomerId LIKE ExtOrders.CustomerId
  11. ORDER BY OrderDate DESC)
  12. ORDER BY CustomerId; -- Might not work in SQLFiddle if CustomerId is text
  13.  
  14. SELECT CustomerId, OrderDate
  15. FROM Orders as ExtOrders
  16. WHERE OrderId IN (
  17. SELECT TOP 4 OrderId
  18. FROM Orders
  19. WHERE CustomerId LIKE ExtOrders.CustomerId
  20. ORDER BY OrderDate DESC)
  21. ORDER BY CustomerId; -- Might not work in SQLFiddle if CustomerId is text
  22.  
  23. SELECT CustomerID, OrderDate
  24. FROM (SELECT o.*,
  25. ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY OrderDate DESC) as seqnum
  26. FROM Orders
  27. ) o
  28. WHERE seqnum <= 4
  29. ORDER BY CustomerID;
  30.  
  31. Use Northwind_2012
  32. WITH e
  33. AS (
  34. SELECT ROW_NUMBER() OVER (
  35. PARTITION BY CustomerID ORDER BY OrderDate DESC
  36. ) AS rownum
  37. ,CustomerID
  38. ,OrderDate
  39. FROM Orders
  40. )
  41. SELECT CustomerID
  42. ,OrderDate
  43. FROM e
  44. WHERE rownum <= 4
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement