Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a SQL Server database in which I ran a query that collects the data about two different tournamnets and the number of people enrolled in the tournament. So for instance the output is:

Name of Tourney    People enrolled
Tourney 1 ----------- 7
Tourney 2 ----------- 8

After getting these two values, I want to be able to compare them and only show the information for the tournmanet that contains the highest number of people enrolled. Should all be in one query...how can I do this?

share|improve this question

closed as off topic by Sven, Michael Hampton, mdpc, Ward, Dave M Apr 21 '13 at 16:43

Questions on Server Fault are expected to relate to professional server, networking, or related infrastructure administration within the scope defined by the community. Consider editing the question or leaving comments for improvement if you believe the question can be reworded to fit within the scope. Read more about reopening questions here.If this question can be reworded to fit the rules in the help center, please edit the question.

    
Are all of the results delimited the same way? –  tacos_tacos_tacos Apr 20 '13 at 1:15
    
Yes they are. I am new to Database but I was doing a COUNT on People AS 'People Enrolled' which gives me the values of 7 and 8. I want to then say since 8 is the largest value, here is the name of the tournamnet (maybe some details as well) –  user168403 Apr 20 '13 at 1:23
    
@jscott beacuse the tourney with the least number of people cannot be in the result –  user168403 Apr 20 '13 at 1:33

1 Answer 1

up vote 2 down vote accepted

On MS SQL Server, this should do the trick:

select top 1 ...
from ...
where ...
order by "people enrolled" desc

This sorts your results DESC so that the row with the largest number of people enrolled is first. Then returns the TOP 1 record - that is, the first row, thus the row with the largest number of people enrolled.

share|improve this answer
    
I only can have a single result, the largest number of people enrolled –  user168403 Apr 20 '13 at 3:00
    
that's what TOP 1 , with the ORDER BY will do - sort the result to have the largest number of people enrolled as first record and return only the TOP 1 row (i.e. the first row). –  ETL Apr 20 '13 at 3:15
    
Ahh...I see that now, sorry i am new to database. That works thank you!! –  user168403 Apr 20 '13 at 3:33

Not the answer you're looking for? Browse other questions tagged or ask your own question.