Select all rows that have column value larger than some value

Question

I have a SQL table thus:

username  |  rank 
a         |  0
b         |  2
c         |  5
d         |  4
e         |  5
f         |  7
g         |  1
h         |  12

I want to use a single select statement that returns all rows that have rank greater than the value of user e's rank.

Is this possible with a single statement?

Solution 1

Yes it is possible.

SELECT * FROM MyTable 
WHERE rank > (SELECT Rank FROM MyTable WHERE username = 'e')

or you can also use self-join for the same

SELECT t1.* FROM MyTable t1
  JOIN MyTable t2
    ON t1.Rank > t2.Rank
   AND t2.username = 'e';

See this SQLFiddle

Solution 2

You can use subquery

SELECT * FROM `table` WHERE `rank` > (
SELECT `rank` FROM `table` WHERE `username` ='b' LIMIT 1)

Solution 3

This is just an edit script of @UweB.. It will return the max rank even if there are multiple rows for username='e'

SELECT *
FROM tbl 
WHERE
tbl.rank > (
   SELECT max(rank) FROM tbl WHERE username = 'e')

Solution 4

SELECT *
FROM tbl 
WHERE
   tbl.rank > (
       SELECT rank FROM tbl WHERE username = 'e'
   );

Note that this will only work if the sub-select (the part in brackets) returns a single value (one row, one column so to speak) only.

View more solutions