CODEWITHSUNDEEP
sqldatabaseoracle-database
Alex
Alex

Reputation: 258

Invalid identifier error, Oracle

I've been trying to write this query for 1 hour, but the SQL Developer always throws an error.

SELECT d.driver_name, COUNT(*) AS cnt
  FROM Drivers d
  JOIN Fastest_laps fl ON d.ID_driver = fl.ID_driver
  GROUP BY d.driver_name
  HAVING cnt = MAX(cnt);
  1. 00000 - "%s: invalid identifier"

Error at last line, column 20.

So I've figured out another solution, but another error is thrown:

SELECT d.driver_name, COUNT(*) as cnt
  FROM Drivers d
  JOIN Fastest_laps fl ON d.ID_driver = fl.ID_driver
  GROUP BY d.driver_name
  HAVING COUNT(*) = MAX(COUNT(*));
  1. 00000 - "group function is nested too deeply"

Error at last line, column 25.

EDIT: thanks gyus, you are awsome, almost all the replies are working, but I have to choose one...

Upvotes: 1

Views: 593

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

Use a window function:

SELECT driver_name, cnt
FROM (SELECT d.driver_name, COUNT(*) AS cnt,
             MAX(COUNT(*)) OVER () as MAXcnt
      FROM Drivers d JOIN
           Fastest_laps fl
           ON d.ID_driver = fl.ID_driver
      GROUP BY d.driver_name
     ) d
WHERE cnt = MAXcnt;

You can also express this using RANK() or DENSE_RANK():

SELECT driver_name, cnt
FROM (SELECT d.driver_name, COUNT(*) AS cnt,
             RANK() OVER (ORDER BY COUNT(*) DESC) as seqnum
      FROM Drivers d JOIN
           Fastest_laps fl
           ON d.ID_driver = fl.ID_driver
      GROUP BY d.driver_name
     ) d
WHERE seqnum = 1;

The advantage to this approach is that you can use ROW_NUMBER() instead and get exactly one row, even if multiple drivers have the same maximum.

Upvotes: 2

Patrick Hofman
Patrick Hofman

Reputation: 156918

You have to wrap your query into a inline view to be able to query cnt:

select *
from   (
         SELECT d.driver_name, COUNT(*) AS cnt
         FROM Drivers d
         JOIN Fastest_laps fl ON d.ID_driver = fl.ID_driver
         GROUP BY d.driver_name
       ) x
group
by     driver_name, cnt
having cnt = MAX(cnt);

Upvotes: 1

Utsav
Utsav

Reputation: 8093

Try this. I ordered by cnt in descending order. And then selected the top row from it. You can edit the query as rownum <=2 to get the top 2 rows and so on.

           with tbl1 as
           (SELECT d.driver_name as driver_name, COUNT(*) AS cnt
            FROM Drivers d
            JOIN Fastest_laps fl ON d.ID_driver = fl.ID_driver
            GROUP BY d.driver_name
            order by cnt desc
            )
            select driver_name,cnt from tbl1
            where cnt = (select cnt from tbl1 rownum=1)

Upvotes: 1

jarlh
jarlh

Reputation: 44696

I'm not sure if Oracle supports this, but please give it a try:

SELECT d.driver_name, COUNT(*) as cnt
FROM Drivers d
  JOIN Fastest_laps fl ON d.ID_driver = fl.ID_driver
GROUP BY d.driver_name
ORDER BY cnt DESC
FETCH FIRST 1 ROW WITH TIES

Or use a common table expression:

with cte as
(
SELECT d.driver_name as driver_name, COUNT(*) AS cnt
FROM Drivers d
  JOIN Fastest_laps fl ON d.ID_driver = fl.ID_driver
GROUP BY d.driver_name
)
select driver_name, cnt
from cte
where cnt = (select max(cnt) from cte)

Upvotes: 1

Related Questions