1

I'm having these schema STUDENT (Sid, Name, City, Adm_date, Paper1, Paper2)

And I want to find the maximum number of students were admitted in which date and the city where maximum number of students lives.

I'm using these queries in ORACLE 11g Express Edition: For City:

SELECT COUNT(Sid) AS No_of_students, City
FROM STUDENT
GROUP BY City
ORDER BY No_of_students DESC
LIMIT 1;

And For Date:

SELECT COUNT(Sid) AS No_of_students, Adm_date
           FROM STUDENT
           GROUP BY Adm_date
           ORDER BY No_of_students DESC
            LIMIT 1;

But here I'm getting error for both these tables: ORA-00933: SQL command not properly ended

Hames
  • 27
  • 4
  • Oracle doesn't support the `limit` keyword. Later versions have [a row-limiting clause](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SELECT.html#GUID-CFA006CA-6FF1-4972-821E-6996142A51C6__BABBADDD), but in 11g you're stuck with an ordered subquery and `rownum`, or a subquery with a ranking function you can then filter on. – Alex Poole Feb 07 '22 at 20:30

2 Answers2

2

Please try rownum with subquery (Since Limit is not available in Oracle):

For city wise:

select * from 
(SELECT COUNT(Sid) AS No_of_students, City
FROM STUDENT
GROUP BY City
ORDER BY No_of_students DESC) where rownum=1

For Adm_date wise:

select * from 
(SELECT COUNT(Sid) AS No_of_students, Adm_date
FROM STUDENT
GROUP BY Adm_date
ORDER BY No_of_students DESC) where rownum=1

Found a great article explaining the mechanism of selecting top-N rows using rownum:

https://blogs.oracle.com/oraclemagazine/post/on-rownum-and-limiting-results

Kazi Mohammad Ali Nur Romel
  • 15,301
  • 2
  • 13
  • 26
  • 1
    This works, but keep two things in mind: 1. In case of ties you pick a date or city arbitrarily. 2. This is an old Oracle propriatary technique, and Oracle has to violate the SQL standard here. (Sub)Query results are unordered data sets by definition, but Oracle keeps them ordered for you, if you apply `ROWNUM` on it. Better use standard SQL window functions, such as `ROW_NUMBER`, `RANK` etc. which are available in Oracle 11g. – Thorsten Kettner Feb 07 '22 at 20:39
2

LIMIT is not part of Oracle's SQL dialect. In Oracle you would use the standard SQL FETCH FIRST clause instead, but in the old version you are using this is not available either.

And whatever approach you are following, keep in mind that there can be ties, i.e. more than one city or date with the maximum number of students. So LIMIT 1 would jump to short. FETCH FIRST does have a ties clause, but as mentioned, it is not available in your old Oracle version.

In Oracle 11g I'd use a window function to solve this:

SELECT city, no_of_students
FROM
(
  SELECT
    city,
    COUNT(sid) AS no_of_students,
    MAX(COUNT(sid)) OVER () AS max_no_of_students
  FROM STUDENT
  GROUP BY city
) counted
WHERE no_of_students = max_no_of_students;
Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73