CODEWITHSUNDEEP
mysqlrow-number
ahamed sakir
ahamed sakir

Reputation: 81

Displaying row number in mysql

I have user table like this .also following 2 fields are fixed . userId is primary key.

userId    name
898        abc
900        xyz
902        rrr
904        wer

if i fetch particular userid then result should display as row number. for example userid 902 then result should display 3.

how to write SQL query?

Upvotes: 2

Views: 7433

Answers (3)

Vincent Omondi
Vincent Omondi

Reputation: 25

The easier way out.

$row_number = 1;
$sql = "SELECT column1, column2 ORDER BY column1";
$result = mysqli_query($conn, $sq1);

while($row = mysqli_fetch_row($result))
{

echo "<tr><td>".$row_number++."</td><td>".$row['0']."</td><td>".$row['1']."</td> 
</tr>";
}

Upvotes: -1

plain jane
plain jane

Reputation: 1007

Try this

    SET @a:=0;

    SELECT  @a:=@a+1 serial_number, 
          userId, name 
    FROM users;

Here is the SQL Fiddle

N.B- Although you should add a PK as auto increment to the table not just to get the serial number but to add index to your table to fetch the records faster

Upvotes: 2

Vinod VT
Vinod VT

Reputation: 7159

Try this

set @row_num = 0; 
SELECT user_id,name, @row_num := @row_num + 1 as index FROM tablename
ORDER BY user_id desc;

Upvotes: 0

Related Questions