Querying a query results?? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Querying a query results??

Author

Topic

tlongarms
Starting Member

1 Post

Posted - 2013-03-14 : 20:19:19

HI,

I am quite new to SQL and and have looked at many examples but have no idea how to make my query work. I ran the following query:

SELECT name, (SELECT population/area) AS density
FROM country

which listed all my country's and their population density (which was population divided by area). However I want to run a query on the results that I got here to give me just one row that will return the country with the MAX density. Is this possible, if so can you tell me how as everything I try and do just wont work?

Thanks for any help!

Terry

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-14 : 20:58:13

Couple of different ways to get the highest density:

select TOP (1) with ties name, 1.0*population/area as density
from country
order by density desc 


;with cte as
(
	select name, 1.0*population/area as density,
	RANK() over(order by 1.0*population/area desc) as RNK
	from country
)
select name, density from cte where RNK = 1;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources