SELECT with certain number of records

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

General SQL Server Forums

New to SQL Server Programming

SELECT with certain number of records

Author

Topic

jmarkee
Starting Member

9 Posts

Posted - 2009-05-08 : 21:29:35

I need to do SQL Select command that filters the number of orders of a particular customer are displayed. My table has multple customers and multiple entries for a customers who have more than one purchase. I would like to have a query that shows me the last 5 orders for each customer?

How would I do this?

Thanks!

jmarkee
Starting Member

9 Posts

Posted - 2009-05-08 : 21:51:29

For instance:

TABLE:

CustomerID Purchase
---------- --------
Jason Teddy Bead
Jason Gorilla
Jason Bike
Jenny Bike

Desired Result if I wanted top 2 record for a customer

Jason Bike
Jason Gorilla
Jenny Bike

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-05-08 : 22:09:11

SELECT CustomerID, Purchase FROM myTable T
WHERE T.Purchase IN(SELECT TOP 2 Purchase FROM myTable T2 WHERE T.CustomerID=T2.CustomerID ORDER BY T2.Purchase)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-05-09 : 01:15:33

Also refer
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/09/12/return-top-n-rows.aspx

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-09 : 04:35:23

quote:
Originally posted by jmarkee

For instance:

TABLE:

CustomerID Purchase
---------- --------
Jason Teddy Bead
Jason Gorilla
Jason Bike
Jenny Bike

Desired Result if I wanted top 2 record for a customer

Jason Bike
Jason Gorilla
Jenny Bike

do you have a date column in your purchase table which designates when order was placed to find the last 5 orders? or if pk of table is purchaseid which i assume is identity field, you can use below


SELECT c.CustomerID,p.Purchase
FROM Customer c
CROSS APPLY (SELECT TOP 5 Purchase
             FROM Purchase
             WHERE CustomerID=c.CustomerID
             ORDER BY PurchaseID DESC) p

jmarkee
Starting Member

9 Posts

Posted - 2009-05-09 : 16:47:08

Thanks Everyone. These worked! You guys rock.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-10 : 02:19:19

welcome

jmarkee
Starting Member

9 Posts

Posted - 2009-05-11 : 17:01:05

One more follow up question. I simplified my question a bit but when I went to deploy i got stuck. I am doing all of this as part of a join statement. It would look something like this:

SELECT CustomerID, Purchase, OtherPurhcaseInfo FROM myTable T INNER JOIN PURCHASE ON mytable.purchaseID = mypurchaseTable.PurchaseID

WHERE T.Purchase IN(SELECT TOP 2 Purchase FROM myTable T2 WHERE T.CustomerID=T2.CustomerID ORDER BY T2.Purchase)

When I add the join statement in I cant seem to get this to work anymore.

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-12 : 09:59:24

you're using wrong aliases. try this & see if it works


SELECT T.CustomerID, T.Purchase, T.OtherPurhcaseInfo 
FROM myTable T 
INNER JOIN PURCHASE P
ON T.purchaseID = P.PurchaseID

WHERE T.Purchase IN(SELECT TOP 2 T2.Purchase FROM myTable T2 WHERE T.CustomerID=T2.CustomerID ORDER BY T2.Purchase)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources