CODEWITHSUNDEEP
sqlmysql
Paulraj
Paulraj

Reputation: 3397

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

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.

Upvotes: 2

Views: 1176

Answers (1)

Quassnoi
Quassnoi

Reputation: 425331

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'

Upvotes: 2

Related Questions