complex SELECT

[Han Xu]

Hi,

I have problem about writing a proper SELECT query for the following
goal:

Table name: peoplelist
column 1: id (not NULL, auto_incremental)
column 2: name
column 3: country

now, there are about 7,000 rows in this table. I want to select out:
first 10 or less people in the table for each country.

for example: suppose there are :

1000 people from US
3000 people from UK
3000 people from Canada

I want to list totally 30 people, i.e. 10 people from each country.

The problem is , the actual table includes many countries, not only
three. How can I do this by a SELECT sql query ?

Thanks.

Han

[David Portas]

Assuming you want the 10 people from each country based on their IDs (10
lowest values), one of the following queries should do it.

CREATE TABLE People (id INTEGER PRIMARY KEY, name VARCHAR(30) NOT NULL
UNIQUE, countrycode CHAR(2) NOT NULL /* REFERENCES Countries (countrycode)
*/)

Standard SQL:

SELECT P.id, P.name, P.countrycode
FROM People AS P
WHERE id IN
(SELECT P1.id
FROM People AS P1
JOIN People AS P2
ON P1.id >= P2.id
AND P1.countrycode = P.countrycode
AND P2.countrycode = P.countrycode
GROUP BY P1.id
HAVING COUNT(*)<=10)

TSQL using TOP:

SELECT P.id, P.name, P.countrycode
FROM People AS P
WHERE id IN
(SELECT TOP 10 id
FROM People
WHERE countrycode = P.countrycode
ORDER BY id)

--
David Portas
SQL Server MVP
--