Solved

# Select TOP x by user

Posted on 2013-01-22
295 Views
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
0
Question by:lrbrister
• 2
• 2

LVL 23

Expert Comment

``````SELECT T1.UserId, T1.ttlRemaining FROM table1 T1
INNER JOIN table2 T2 ON T1.UserId = T2.UserId
GROUP BY  T1.UserId, T1.ttlRemaining
``````
Try that
0

LVL 23

Expert Comment

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
``````
0

LVL 31

Expert Comment

>>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?
0

Accepted Solution

lrbrister earned 0 total points
Hey guys,
Foundmy own answer by using row_numbers.

What I'm actually accomplishing is this...
Some data folks want the ability to send emails to certain groups anytime they want.

These groups may have up to 1000 members

Management says sure...lrbrister, give them that ability but limit how many a day they can actually send.

So...caught in the middle of a no-win situation like Dilbert
And with management setting what the "total by person" can be...
I've applied an arbitray order by and taken the top "x" rows (remaining)

``````SELECT	ROW_NUMBER() OVER (PARTITION BY [fromUser] ORDER BY fromUser) AS Seq,
l.email,
e.encString querystring ,
9 CampaignTypeID,
a.remaining
INTO	#temp
FROM	dbo.EmailBlastQueueFR e
OUTER APPLY
(
SELECT * FROM EmailBlastQueueFRDailyTotal ET
WHERE ET.sm = e.fromUser
) A
WHERE sent = 0
ORDER BY fromUser,seq

SELECT * INTO #Results FROM #temp WHERE Seq < = remaining AND remaining > 0
``````
0

Author Closing Comment

0

## Featured Post

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.