More elegant SQL?

Question

Hi,

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

Depends on the database you use, but try something like this:

oracle: select username from table where rownum < 50 order by userid;

postgresql: select username from table order by userid limit 10;

Answer 2

Purely for aesthetic I use SQLinForm which is a Java applet that formats your SQL very nicely. It's flexible with a lot of options too.

Answer 3

how about:

SELECT username FROM table ORDER BY userid DESC LIMIT 50

Answer 4

If you would have a DateCreated field in the user table, you could make the query like this:

SELECT TOP 50 username
FROM table 
ORDER BY DateCreated DESC

Answer 5

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

Answer 6

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 7

You can use ROW_NUMBER() to assign a number to each row, based on its position in an ordered list. For example, to number rows starting with 1 for the highest userid:

SELECT username
FROM (
    SELECT 
         ROW_NUMBER() OVER (ORDER BY user_id DESC) AS RowNr,
         *
    FROM users
) sub
WHERE RowNr < 50

Unlike the query in the question, this will work if userid is not consecutive.