Trying to get a top 20 count of users from SQL

[zmoney]

HI,
I am trying to get the top 20 users from my database and order it by the name. All help is greatly appreciated.
Z


Select Top 20 FullName, Counter
(
SELECT count(tixq.tixid) as counter, UserProfile.FName + ' ' + UserProfile.LName AS FullName
FROM TixQ, UserProfile
WHERE UserProfile.UID=TixQ.Create_For
GROUP BY UserProfile.FName + ' ' + UserProfile.LName

UNION

SELECT Count(dbo.TixArchive.TixID) as counter, dbo.UserProfile.FName + ' ' + dbo.UserProfile.LName As FullName
FROM dbo.TixArchive, dbo.UserProfile
WHERE dbo.TixArchive.Create_For=dbo.UserProfile.UID
GROUP BY dbo.UserProfile.FName + ' ' + dbo.UserProfile.LName
)

ORDER BY Counter desc

 

[redbank]

Hi,

I have a similar problem.

Did you find a solution ?

 

[r937]

looks okay to me except for the missing FROM keyword )

rudy

http://rudy.ca/

 

[petersJazz]

Dont look like standard SQL to me. Top 20 is not standard and the syntax is wrong a well (for standard SQL).

A union with order by and then read the 20 first rows.

 

[r937]

petersJazz, you are totally right, i completely missed the opportunity to point out that TOP is not standard

"read the 20 first rows" is not a great solution either, but i think i know why you suggested that


zmoney, are you there? did it work when you added the missing FROM keyword?

 

[angiole]

I'm guessing here, but if the Order By Counter desc is part of the inner query, then the TOP 20 would be satisfied, and you can place another Order by at the end to sort the output.

AA 8~)

 

[crufty]

To get the top N without using the top function (say you're working with Oracle 8i) you have to first order the resultset in the manner you want (i.e in this example, by counter) then select the first 20 rows of the ordered resultset. For oracle, use ROWNUM to limit the resultset for the first 20 rows (I'm sure other DB's have their own methods of select a certain number of rows from a resultset. Consult your DB's docvumentation for specifics)

In pseudo Oracle code:

select name, count
from
(
select name, count
from
( main union query )
order by count desc
)
where rownnum < 21 ;

The outer query selects the first 20 rows of the inner query that orders the results of the main union query.

Side note, for an Oracle query, the ordering inner query is not needed as you can perform order by on a union query as long as you use absolute column position rather than column name.

 

[angiole]

The '+' notoation between the column names says it's not Oracle.

It's a safe guess it's Access or SQL Server.
AA 8~)

 

[r937]

the original query, with missing FROM keyword inserted, was

Select Top 20 FullName, Counter 
  from  ( union query )
ORDER BY Counter desc

and the original question was &quot;get the top 20 users from my database and order it by the name&quot;

so angiole was right on, it requires a resort

Select FullName, Counter 
  from 
       ( Select Top 20 FullName, Counter 
           from ( union query )
         ORDER BY Counter desc )
order by FullName

rudy

http://rudy.ca/

 

[angiole]

Rock & Roll ... AA 8~)