Syed Qasim Ahmed Syed Qasim Ahmed - 8 months ago 62
SQL Question

Query simplification Oracle Northwind

Task :

Find country to which maximum of customers belong.

Query

SELECT country,
count(*)
FROM customers
GROUP BY country
HAVING count(*) =
(SELECT max(max_sal)
FROM
(SELECT count(*) max_sal
FROM customers
GROUP BY country)) ;


Result:

enter image description here

The Result is correct but i think it is difficult way to write query

Question : Is there any simple way to rewrite this query.

Answer Source

I might be missing something, but it can be as simple as this:

SELECT *
  FROM (  SELECT country, COUNT (*) max_sal
            FROM customers
        GROUP BY country
        ORDER BY COUNT (*) DESC)
 WHERE ROWNUM <= 1;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download