Search code examples
mysqlwhere-clauserow-number

MySQL - return row number where value=3

I want to use a MySQL query which returns all row numbers which are matching a certain value.

I do already have an attempt that is the following:

SELECT t.*, @rownum := @rownum + 1 AS rank FROM `TABLE` t, (SELECT @rownum := 0) r

This returns all values which are stored inside the table "TABLE" with an additional column "rank" showing the row number.

So how do I adapt this code snippet to only get the "rank" column and how do I get a "where column1 = 3" query to that MySQL request?

Thank you for helping in advance!

Solution

  • Put your code in a subquery and test for what you want in the main query.

    SELECT rank
FROM (
    SELECT t.*, @rownum := @rownum + 1 AS rank 
    FROM `TABLE` t, (SELECT @rownum := 0) r
) AS x
WHERE column1 = 3