Sql query - 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 2000 Forums

SQL Server Development (2000)

Sql query

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-08-16 : 09:27:06

Vaidya writes "I have a table of countries,regions with their respective populations.

Some countries have populations more than three times that of any of their neighbours (in the same region).

i would like to have those countries and regions."

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-16 : 09:31:15

How do you get that this is the Neighbouring country??

Chirag

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-16 : 10:22:25

Within same region I think.

Peter Larsson
Helsingborg, Sweden

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-16 : 10:25:54

Vaidya, u may need to post ur table structure, sample data & expected results.

Srinika

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-16 : 10:28:42

This will do, I believe

SELECT DISTINCT	mt.Region,
		mt.Country,
		mt.Population
FROM		MyTable mt
INNER JOIN	(
			SELECT		Region
					MAX(Population) MaxPop
			FROM		MyTable
			GROUP BY	Region
		) dt ON dt.Region = mt.Region AND dt.MaxPop >= 3 * mt.Population

Peter Larsson
Helsingborg, Sweden

mobil750
Starting Member

2 Posts

Posted - 2008-02-13 : 12:02:17

Hi,
i have the same problem as this exercise is on the http://sqlzoo.net/1a.htm !
It is the last exercise on this page, concretely the 3c.

Peso's solution does not work, the site doesn't accept it so if someone else has a better idea, please help!

Thanks in advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-13 : 12:17:25

Here is a version that works for MySQL too...

SELECT	t1.Name,
	t1.Region
FROM	bbc AS t1
WHERE	t1.Population >= 3 * (SELECT MAX(t2.Population) FROM bbc AS t2 WHERE t2.Name <> t1.Name AND t2.Region = t1.Region)

E 12°55'05.25"
N 56°04'39.16"

mobil750
Starting Member

2 Posts

Posted - 2008-02-14 : 03:01:33

it works for oracle too, thanks for the quick reply ;)

I had missed only the part t2.Name <> t1.Name before, but it's necessary.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources