1

How can i get the nth row position from table without using limit ?

I have a table with four fields id,name,country,description. The query is returning by condition country = 'asia'. The total number of records is more than hundred. Now what i need is if i hav a name 'test' in 23rd position in table then how can i get the position 23rd without using limit and auto increment id ( auto id doesn't give the exact position where i am using a condition over my query)

name should not contain the duplicate record.

I just want the position to check through another query (for pagination).

Is there any function available in mysql to achieve this with out using limit?

I have got some idea through this but i cant get my expected mysql result through the answers.

Thanks.

Community
  • 1
  • 1
Paulraj
  • 3,373
  • 3
  • 36
  • 40

1 Answers1

2

Since SQL does not have concept of implicit "row position", you should define the row order by sorting the rows on a column or an expression.

This assumes your row positions are identified by the field id (i. e. rows with higher id come later).

SELECT  (
        SELECT  COUNT(*)
        FROM    mytable mi
        WHERE   country = 'asia'
                AND mi.id <= mo.id
        )
FROM    mytable mo
WHERE   country = 'asia'
        AND name = 'test'
Quassnoi
  • 413,100
  • 91
  • 616
  • 614