Solved

Complex Select Statement

Posted on 2011-03-03
3
526 Views
Last Modified: 2012-05-11
I an trying to assemble a query that does the following in psudo code but in a single query without using a temp table.  I'm not sure how to do it.  Can someone help?

Query 1
SELECT Distinct UserID
FROM UserTable

Query 2
Start Loop of results from query 1)
  SELECT count(*) as RecordCount
  FROM UserTable
  WHERE UserID = UserID (from query 1)
End Loop of results from query 1)

I put all the results of query 2 into a temp  table.

Then I query the temp table as follows.

SELECT UserID
FROM Query2ResultSet
ORDER BY RecordCount desc
Display Top 10

Thanks in advance.
0
Comment
Question by:SFTProd
[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
3 Comments
 

Author Comment

by:SFTProd
ID: 35032633
I meant the above was in psudo code.  I am trying to put together a real sql query that will run in MSSQL 2008 R2.

Thanks.
0
 
LVL 32

Accepted Solution

by:
Ephraim Wangoya earned 500 total points
ID: 35032650
 SELECT top 10 UserID, count(*) as RecordCount
  FROM UserTable
  group by UserID
  order by count(*) desc
0
 

Author Comment

by:SFTProd
ID: 35032701
Thank You!
0

Featured Post

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

688 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