CODEWITHSUNDEEP
mysql
Schadrack Rurangwa
Schadrack Rurangwa

Reputation: 411

How can I select from in other select in MYSQL query?

How can I select from in other select in MYSQL query? Something like this

SET @row_number = 0;

SELECT a.num FROM

(SELECT 
    (@row_number:=@row_number + 1) AS num, id
FROM
    main) as a where a.id=6

I want to know the number of records where id=6 if it's the first row, second row or third one

Upvotes: 0

Views: 78

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

If your query has the filter where a.id = 6, then the row with id = 6 will always be the first row of the result set.

I am interpreting your question to mean: "if I sorted by id ascending, what row number is the row with id = 6 going to be on". If so, you can use a simple aggregation:

SELECT COUNT(*)
FROM main m
WHERE m.id <= 6;

Your query seems inspired by enumerating all the rows. You could do this version as well:

select m.*
from (select m.*, (@rn := @rn + 1) as rn
      from main m cross join
           (select @rn := 0) params
      order by id
     ) m
where id = 6;

The first version should be more efficient, particularly with an index on id.

Upvotes: 1

Related Questions