Larry Brister
asked on
Select TOP x by user
I have two tables
Table1 has userid and ttlRemaining
table2 3 columns
col1, col2, userid
Table2 has thousands of records in it and anywhere from 100-150 distinct userid's
I need to do a Select top ttlRemaining * from table2 by userid
So...if userid1234 has 30 remaining and userid5478 has 20 remaining
I get 50 total records back on my select
Table1 has userid and ttlRemaining
table2 3 columns
col1, col2, userid
Table2 has thousands of records in it and anywhere from 100-150 distinct userid's
I need to do a Select top ttlRemaining * from table2 by userid
So...if userid1234 has 30 remaining and userid5478 has 20 remaining
I get 50 total records back on my select
And this if you want to show the number that there are in Table2
SELECT T1.UserId, T1.ttlRemaining, count(*) as T2_Count FROM table1 T1
INNER JOIN table2 T2 ON T1.UserId = T2.UserId
GROUP BY T1.UserId, T1.ttlRemaining
>>So...if userid1234 has 30 remaining<<
I'm not fully understanding what you're trying to accomplish. Are you saying if there is a record in table1 that has a value of 1234 for userid and a value of 30 for ttlRemaining, there are more than 30 records for that userid in table2 but you only want the top 30? If that's the case, what defines the top?
I'm not fully understanding what you're trying to accomplish. Are you saying if there is a record in table1 that has a value of 1234 for userid and a value of 30 for ttlRemaining, there are more than 30 records for that userid in table2 but you only want the top 30? If that's the case, what defines the top?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Found my own answer
Open in new window
Try that