CODEWITHSUNDEEP
mysqlsqlsubquery
lxcky
lxcky

Reputation: 1668

Using result in the outer query in the inner query

What I'm trying to do is to query all the unique district name from the database, and on each district name, get its corresponding MIN and MAX ID.

Here's what I have right now:

SELECT DISTINCT DISTRICTNAME, 
(SELECT MIN(`ID`)
    FROM [DATABASE_NAME]
        WHERE DISTRICTNAME = DISTRICTNAME) AS 'MIN', 
(SELECT MAX(`ID`) 
    FROM [DATABASE_NAME]
        WHERE DISTRICTNAME = DISTRICTNAME) AS 'MAX'
FROM [DATABASE_NAME]
WHERE [CONDITION]
ORDER BY DISTRICTNAME;

Problem: The MIN and MAX column shows the same value. I'm not really sure about my inner query's WHERE clause. I think that's the problem.

Help?

Upvotes: 0

Views: 34

Answers (1)

Robert
Robert

Reputation: 25753

Try this query with GROUP BYclause

SELECT DISTRICTNAME, MIN(`ID`) AS 'MIN', MAX(`ID`) AS 'MAX'
FROM [DATABASE_NAME]
WHERE [CONDITION]
GROUP BY DISTRICTNAME
ORDER BY DISTRICTNAME;;

Upvotes: 3

Related Questions