CODEWITHSUNDEEP
sqloracle-databasetable-alias
Somnath Bose
Somnath Bose

Reputation: 47

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;

Upvotes: 1

Views: 3509

Answers (2)

EJ Egyed
EJ Egyed

Reputation: 6084

You need to remove the as keyword from your query. To alias a table, AS is not allowed. AS can be specified to give an alias to a column.

Upvotes: 2

The Impaler
The Impaler

Reputation: 48770

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.

Upvotes: 0

Related Questions