2

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?

ahamed sakir
  • 81
  • 1
  • 6

3 Answers3

2

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

plain jane
  • 1,009
  • 1
  • 8
  • 19
  • it returns always 1. SELECT @a:=@a+1 serial_number, userId, name FROM users, (SELECT @a:= 0) AS a where userId=902; It should return 3 – ahamed sakir Oct 08 '13 at 12:21
  • that is not supposed to return the serial_number while comparing a particular id..... – plain jane Oct 09 '13 at 04:17
  • because we are just adding the serial_number to the output of the query...what you want will need an extra column in database which you dont want – plain jane Oct 09 '13 at 04:19
  • i've been searching high and low for a simple answer to this, and this works beautifully. upvoted, thanks a ton! – Gharbad The Weak Sep 09 '21 at 21:42
0

Try this

set @row_num = 0; 
SELECT user_id,name, @row_num := @row_num + 1 as index FROM tablename
ORDER BY user_id desc;
Vinod VT
  • 6,946
  • 11
  • 51
  • 75
-1

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>";
}
Vincent Omondi
  • 25
  • 9