Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Select TOP x by user

Posted on 2013-01-22
5
Medium Priority
?
302 Views
Last Modified: 2013-01-27
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
Comment
Question by:lrbrister
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 23

Expert Comment

by:apresto
ID: 38806136
SELECT T1.UserId, T1.ttlRemaining FROM table1 T1 
INNER JOIN table2 T2 ON T1.UserId = T2.UserId
GROUP BY  T1.UserId, T1.ttlRemaining

Open in new window

Try that
0
 
LVL 23

Expert Comment

by:apresto
ID: 38806144
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

Open in new window

0
 
LVL 32

Expert Comment

by:awking00
ID: 38806618
>>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

by:
lrbrister earned 0 total points
ID: 38807112
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,
        e.lead_id, 
        fromUser + '@ourdomain.net' fromEmailAddress,
        a.remaining
INTO	#temp
FROM	dbo.EmailBlastQueueFR e
INNER JOIN dbo.leads l
	ON	e.lead_id = l.lead_id
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

Open in new window

0
 

Author Closing Comment

by:lrbrister
ID: 38823813
Found my own answer
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question