Help with Implementing Waitlist

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

Transact-SQL (2005)

Help with Implementing Waitlist

Author

Topic

fishpop
Starting Member

2 Posts

Posted - 2009-09-19 : 21:10:18

Hi, I have a table with records of people who signed up for a waitlist to purchase one or more of several products. I'd like to be able to return a result set with the first n users who added themselves to the waitlist for specific products so I can contact them as the products become available. For example, I have the following table which I use to record their preferences:


WaitlistMemberID	UserID	ProductID	SignupTime
1			543		5		09/01/2009 10:56:01
2			765		5		09/01/2009 16:54:12
3			765		2		09/01/2009 16:59:51
4			879		3		09/03/2009 05:18:25
5			138		5		09/03/2009 17:12:43
6			765		3		09/04/2009 18:15:56
7			132		5		09/04/2009 21:45:38

For example, I might want to get the first two users who signed up for products 3 and 5. In this example the query result would look like:


WaitlistMemberID	UserID	ProductID	SignupTime
1			543		5		09/01/2009 10:56:01
2			765		5		09/01/2009 16:54:12
4			879		3		09/03/2009 05:18:25
6			765		3		09/04/2009 18:15:56

Of course I know the easy part, which is selecting just for the specific products like this:
SELECT *
FROM WaitlistMembers
WHERE ProductID IN (3,5)

I can't figure out how to only get the first two signups (by SignupTime) for each product. Any help is greatly appreciated!

fishpop
Starting Member

2 Posts

Posted - 2009-09-20 : 02:44:50

Well, in case anyone stumbles upon this thread, here's how I accomplished it:


SELECT *
FROM (
SELECT WaitlistMemberID, UserID, ProductID, SignupTime
, ROW_NUMBER() OVER( PARTITION BY ProductID ORDER BY SignupTime ) rn
FROM WaitlistMembers
WHERE ProductID IN (3,5)
) derived
WHERE rn <= 2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources