sqloracle-databasetable-alias

How to filter records having only rank=1 in oracle without using WITH block

Created a sample table and tried filtering records having rank 1 , but its failing

Error

ORA-00933: SQL command not properly ended 00933. 00000 - "SQL command not properly ended" *Cause:
*Action: Error at Line: 30 Column: 3

CREATE TABLE exam_result (
points int,
last_name varchar(255),
first_name varchar(255) );

INSERT INTO exam_result VALUES (70, 'Emyr', 'Downes');
INSERT INTO exam_result VALUES (70, 'Dina', 'Morin');
INSERT INTO exam_result VALUES (80, 'Evie-May', 'Boyer');
INSERT INTO exam_result VALUES (60, 'Nora', 'Parkinson');
INSERT INTO exam_result VALUES (40, 'Trystan', 'Oconnor');
INSERT INTO exam_result VALUES (90, 'Eryk', 'Myers');

SELECT * FROM
(
  SELECT
  RANK() OVER(ORDER BY points DESC) AS ranking,
  first_name,
  last_name,
  points
  FROM exam_result
) AS a
WHERE ranking <= 1;
Solution

  • Remove the AS keyword. For example:

    SELECT * FROM
(
  SELECT
  RANK() OVER(ORDER BY points DESC) AS ranking,
  first_name,
  last_name,
  points
  FROM exam_result
) a
WHERE ranking <= 1;

    See running example at db<>fiddle.