Andrew J Winkler Andrew J Winkler - 6 months ago 19
SQL Question

asking for help factoring SQL code - MS SQL Server

SQL Fiddle is currently down regarding MS SQL Server code, so here is a dropbox link to a .txt containing the DDL to create the schema I'm using:

https://www.dropbox.com/s/6si4r37449q3ajb/DDL.txt?dl=0

I'm studying for an exam and I know there's a more efficient way to code this, I just don't know what it is.

5. Find out the department which has the highest number of personal computers installed.

select top(1) pc.location, count(pc.location) as number_of_comps_in_dept
from pc
group by location
order by number_of_comps_in_dept desc


My code featured above works, but what if I wanted to just get the department name (labeled location in this case)? I can't really call a single value back via my current code - which isn't friendly to procedures, functions, and triggers down the road.

Thank you for your help.

Answer

You can just remove the other columns in your SELECT statement. However, you need to replace the column in the ORDER BY clause with the aggregate:

select top(1) pc.location
from pc
group by location
order by count(pc.location) desc

ONLINE DEMO

Comments