-2

How can I get the row number of an element?

Example Data

id   name   age
8347 Jacob  65
3645 Jerico 67
3243 Michel 34

How can I get the row number of age 67? row number 2

GMB
  • 216,147
  • 25
  • 84
  • 135
Jerico M Navarro
  • 21
  • 1
  • 5
  • 6
    Row number is a rather meaningless concept in relational databases. But you could say "I want to know who is oldest, who is second oldest, and who is youngest" – Strawberry Nov 14 '19 at 16:55
  • Is age a unique field? Do you want all occurrences? or the first one? can we order by any column? – Antonio Ortells Nov 14 '19 at 16:58

2 Answers2

1

This is a common misconception: sql table represents unordered sets of rows. There is no inherent ordering in the data. If you do not use an order by clause, then the order in which rows are returned is not deterministic.

So, to start with, you need to order your records. Let's assume that you want to order them by id (it seems to be a unique column, which is good for ordering since it avoids ties). If you are running MySQL 8.0, you can use row_number() to rank the records:

select *
from (select t.*, row_number() over(order by id) rn from mytable t) t
where age = 67

This record with age 67 will be assigned row number 2, since that's its position when data is sorted by id. If you had several records with age 67, they would all show in the resultset, along with their associated row number.

GMB
  • 216,147
  • 25
  • 84
  • 135
0

From the looks of things this is not a MySQL function rather SQL, as explained in @Strawberry's comment row number is meaningless.

This question looks similar to what you are trying to achieve so please take a look! ROW_NUMBER() in MySQL

J_C
  • 329
  • 1
  • 15