First of all, I am new here and just learning sql, so bear with me.
I have a simple question. Let's say I have a table called voting
with the following columns:
id, token(int), candidate(int), rank(int)
.. and I want to perform a query like:
SELECT *
FROM voting
WHERE rank > t1.rank
AND token = t1.token
.. where t1
is
SELECT rank,token
FROM voting
WHERE candidate = $mycandidate
How can I do this in a single statement using AS alias, or whatever is the simplest method?
Please note that final table created may have rows with different candidates than i have specified i.e the rank,token variables are initially chosen according to candidate but once chosen they may contain any candidate value with them.