Adding Row Number on SQL SELECT

Question

Okay this is going be probably very simple, but I've spent 4 hours on this and I'm giving up to ask what I'm missing. I am doing an SQL SELECT statement:

"SELECT username, id, Days FROM Users ORDER BY Days DESC";

Very simple. But now, when displaying all of my data using this:

if ($result->num_rows > 0) {
echo "<table><tr><th>ID</th><th>Name</th><th>Days</th></tr>";
// output data of each row
while($row = $result->fetch_assoc()) {
    echo "<tr><td>" . **$row["id"].** "</td><td>" . $row["username"]. "</td><td>" . $row["Days"]. "</td></tr>";
}
echo "</table>";
} else {
echo "0 results";
}

I would like the first one replacing the bold $row['id'] to be the row number.

I have tried all of the following:

SELECT *, number = ROW_NUMBER() OVER (ORDER BY Days) FROM Users AS rownumber ORDER BY Days;


SELECT ROW_NUMBER() OVER (Order by Days) AS 'RowNumber', username FROM Users

SELECT  username, Days, id, ROW_NUMBER() OVER(ORDER BY Days) AS 'RowNumber' FROM Users";

^^ all of which were things I found on internet. None have worked. Am I missing something blindly obvious here?

score 2 · Accepted Answer · answered Dec 02 '17 at 21:00

If you are using a programming language, you can keep your SQL simple:

"SELECT username, id, Days FROM Users ORDER BY Days DESC";

Then process the row number using the programming language:

if ($result->num_rows > 0) {
$rowNumber=0;
echo "<table><tr><th>ID</th><th>Name</th><th>Days</th></tr>";
// output data of each row
while($row = $result->fetch_assoc()) {
    $rowNumber++;
    echo "<tr><td>" . $rowNumber "</td><td>" . $row["username"]. "</td><td>" . $row["Days"]. "</td></tr>";
}
echo "</table>";
} else {
echo "0 results";
}

Caius Jard · Answer 2 · 2017-12-02T20:59:20.020

1

MySQL doesn't support analytic queries (window functions) like row_number - for that you have to use oracle (if you want something from oracle corp) that does.

I wouldn't use the database for this; modify your front end code to output an incrementing number as it dumps the rows to HTML- this is a task for presentation code, not the sql query

edited Dec 02 '17 at 20:59

answered Dec 02 '17 at 20:57

Caius Jard

72,509
5
49
80

..... you're so brilliant. Yes duh. Why did I not think of that, thats easy. Lol thank you – London O'Connell Dec 02 '17 at 20:58

score 0 · Answer 3 · answered Dec 02 '17 at 21:01

0

You can use variables. This normally works:

SELECT (@id := @id + 1) as id, username, Days
FROM Users u CROSS JOIN
     (SELECT @id := 0) params
ORDER BY Days DESC;

answered Dec 02 '17 at 21:01

Gordon Linoff

1,242,037
58
646
786

Adding Row Number on SQL SELECT

3 Answers3