SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Querying a query results??
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tlongarms
Starting Member

1 Posts

Posted - 03/14/2013 :  20:19:19  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3575 Posts

Posted - 03/14/2013 :  20:58:13  Show Profile  Reply with Quote
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;
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000