More elegant SQL?

Question

The query below is perfectly functional, and queries a single table to find the last 50 usernames added via a sequential userid number column.

Logic so far is to: find out the highest userid; subtract 50 from that; pull usernames back where greater.

However, it doesn't look elegant, and uses two subqueries to achieve it's goal:

SELECT username  
FROM table  
WHERE userid IN  
  (SELECT userid  
   FROM table  
   WHERE userid >  
    (SELECT MAX(userid) -50  
     FROM table))

Is there a way to make this less nested? More efficient? More elegant? Any help would be much appreciated, as this can't be the best way!

Cheers & many thanks
Ali

Answer 1

The answers provided are along the right lines. You can use ROWNUM to select TOP-N style results.

Please be careful though and note that the rownum is assigned to the query results after predication but before the ORDER BY. Try something like the following:

SELECT username  
FROM 
  (SELECT username  
   FROM table  
   ORDER BY userid DESC)
WHERE rownum <= 50

Answer 2

SELECT * FROM (SELECT UserName FROM Table ORDER BY UserID DESC) 
WHERE RowNum <= 50

... I think... been a while since I fiddled with Oracle.

This is roughly the same as

SELECT Top 50 in SQLServer

and

SELECT ... LIMIT 50 in MySql