CODEWITHSUNDEEP
sqlmysql
Arnaud F.
Arnaud F.

Reputation: 8452

Sql - Use the biggest value to select other data

I've a big doubt on the way to retrieve the biggest value of a table and use it in another query.

Consider this : 

CREATE TABLE people
(
    peopleID int NOT NULL,
    cityID int NOT NULL
)

The following request gives me the number of people per city

SELECT peopleID, COUNT(*)
FROM people
GROUP BY cityID

Suppose I want the people list of the biggest city, I would write this request like :

SELECT people.peopleID, people.cityID
FROM people,
(
SELECT cityID, COUNT(*) AS "people_count"
FROM people
GROUP BY cityID
) g
WHERE people.cityID = g.cityID
HAVING people_count = MIN(people_count)

but doesn't work, what's the best way to achieve this request?

Thanks :)

Upvotes: 0

Views: 260

Answers (1)

Mark Byers
Mark Byers

Reputation: 838796

This technique should work in most databases:

SELECT peopleID
FROM people
WHERE cityID =
(
    SELECT cityID
    FROM people
    GROUP BY cityID
    ORDER BY COUNT(*) DESC
    LIMIT 1
)

LIMIT 1 is not standard SQL (the standard states that you should use FETCH FIRST 1 ROWS ONLY). See here for a list of how to fetch only the first row in a variety of databases:

Edit: I misunderstood your question. I thought you meant what is a sensible way to perform this query that can easily be modified to work in almost any SQL database. But it turns out what you actually want to know is how to write the query so that it will work using exactly the same syntax in all databases. Even random databases that no-one uses that don't even properly support the SQL standard. In which case you can try this but I'm sure you can find a database where even this doesn't work:

SELECT peopleID, cityID
FROM people
WHERE cityID = (
    SELECT MAX(cityID)
    FROM (
        SELECT cityID
        FROM people
        GROUP BY cityID
        HAVING COUNT(*) =
        (
            SELECT MAX(cnt) FROM
            (
                SELECT cityID, COUNT(*) AS cnt
                FROM people
                GROUP BY cityID
            ) T1
        )
    ) T2
)

Upvotes: 5

Related Questions