CODEWITHSUNDEEP
sqloracle-database
bohawk
bohawk

Reputation: 71

"ORA-00933: SQL command not properly ended" error using Group By Clause

I doing a few queries and I keep getting the same "ORA-00933: SQL command not properly ended" error. So here they are:

--Artist of the song that has the most streams 

SELECT ArtistID, NumberofStreams 
FROM Songs 
GROUP BY SongID 
ORDER BY NumberOfStreams DESC 
LIMIT 1;

--Returns oldest album and its Artist 

SELECT ArtistName, AlbumName 
FROM Album 
ORDER BY ReleaseDate ASC 
LIMIT 1;

--Returns Artist with the most songs 

SELECT ArtistID, COUNT(SongID) 
FROM Songs 
GROUP BY ArtistID 
ORDER By COUNT(SongID) DESC 
LIMIT 1;

Tables are set up like this:

Songs(SongID,ArtistID,SongName,SongName,SongLength)

Album(AlbumID,AlbumName,ArtistID,ArtistName,NumberOfTracks,ReleaseDate)

I think it has to do with my the ORDER BY or the LIMIT but I just cannot figure it out

Upvotes: 1

Views: 1913

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

Oracle does not support LIMIT. You can rewrite these with a subquery:

SELECT x.*
FROM (SELECT ArtistID, NumberofStreams 
      FROM Songs 
      GROUP BY SongID 
      ORDER BY NumberOfStreams DESC 
     ) x
WHERE rownum = 1;

Oracle 12c+ supports the much simpler and standard fetch first 1 row only:

      SELECT ArtistID, NumberofStreams 
      FROM Songs 
      GROUP BY SongID 
      ORDER BY NumberOfStreams DESC 
      FETCH FIRST 1 ROW ONLY

Upvotes: 2

Related Questions