MySQL select record if value exists in another record

I need to select records from a table, but only if a value exists 1 greater than a value on the current record and the user is the same.

This is my current table:

    +---------+------------+-----------+
    | user_id | scheme_val | scheme_id |
    +---------+------------+-----------+
    |    1    |   text1    |     1     |
    |    1    |   text2    |     2     |
    |    2    |   text1    |     1     |
    +---------+------------+-开发者_如何学JAVA----------+

So basically I need to select all records, so long as there isn't another record from the same user with a higher scheme_id. So on this table it would only bring back the second and third row and not the first row. The reason being for the first row for user 1, there is another row in the table with a scheme_id 1 greater than that row.

I hope this makes sense?

Thanks in advance.

Try the following query. It will work for sure, but won't be the most optimized one. Its a little out of the box, but i'm very much interested to hear if any one has any other solutions.

SELECT
    *
FROM
    users
WHERE
    CONCAT(user_id, ":", scheme_id)
        IN
            (
                SELECT
                    CONCAT(user_id, ":", MAX(scheme_id))
                FROM
                    users
                GROUP BY
                    user_id
            )

Should be possible with a subquery joining with the same table:

select * from user u1 where u1.scheme_id =
( select max(u2.scheme_id) from user u2
  where u1.user_id = u2.user_id );