Add a row number to result set of a SQL query
The typical pattern would be as follows, but you need to actually define how the ordering should be applied (since a table is, by definition, an unordered bag of rows). One way to do that if you don't care about a specific order otherwise is to use the leading key(s) of a covering index, the leading key(s) of the clustered index, or the columns in any group by / order by clauses. In this case I'll assume A
is the single-column clustering key for t
:
SELECT t.A, t.B, t.C, number = ROW_NUMBER() OVER (ORDER BY t.A)
FROM dbo.tableZ AS t
ORDER BY t.A;
If you truly don't care about order, you can generate arbitrary/nondeterministic row numbering using:
ROW_NUMBER() OVER (ORDER BY @@SPID)
-- or for serial plans
ROW_NUMBER() OVER (ORDER BY @@TRANCOUNT)
Little tricks I picked up from Paul White in this article (see "Paul's Solution").
Not sure what the variables in your question are supposed to represent (they don't match).
Adding Row Number on SQL SELECT
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";
}
How to add row number to an MySQL display using ROW_NUMBER()
Window functions evaluate after GROUP BY
aggregation has happened, so it doesn't make much sense to use a partition on the name
, since each record at that point would be guaranteed to have a distinct name. Most likely, you want something like this:
SELECT
name,
ROW_NUMBER() OVER (ORDER BY AVG(work_days), COUNT(*) DESC) AS row_num,
AVG(work_days) AS workday_average,
COUNT(*) AS count
FROM work
GROUP BY
name
HAVING
workday_average > 2
ORDER BY
workday_average,
count DESC;
But this of course assumes that you are using MySQL 8+. If not, then ROW_NUMBER
won't be available.
Add a row number to a microsoft access query
One way to do it is with a correlated subquery:
SELECT s.pluquo,
(SELECT COUNT(*)
FROM (SELECT DISTINCT pluquo FROM SYNC002_ACCESS) AS t
WHERE t.pluquo <= s.pluquo
) AS row_number
FROM (
SELECT DISTINCT pluquo
FROM SYNC002_ACCESS
) AS s
Or with a self join:
SELECT s1.pluquo, COUNT(*) AS row_number
FROM (SELECT DISTINCT pluquo FROM SYNC002_ACCESS) AS s1
INNER JOIN (SELECT DISTINCT pluquo FROM SYNC002_ACCESS) AS s2
ON s2.pluquo <= s1.pluquo
GROUP BY s1.pluquo
Add row number to this T-SQL query
Add: ROW_NUMBER() OVER (ORDER BY NumCars)
EDIT:
WITH t1 AS
( SELECT DISTINCT
VehicleSpecs.SubmittedById ,
COUNT(VehicleSpecs.SubmittedById) AS NumCars ,
aspnet_Users.UserName
FROM VehicleSpecs
INNER JOIN aspnet_Users ON VehicleSpecs.SubmittedById = aspnet_Users.UserId
WHERE ( LEN(VehicleSpecs.SubmittedById) > 0 )
GROUP BY VehicleSpecs.SubmittedById ,
aspnet_Users.UserName
)
SELECT ROW_NUMBER() OVER ( ORDER BY NumCars ), *
FROM t1
ORDER BY NumCars
Related Topics
Storing Multiple Choice Values in Database
Converting Aggregate Operators from SQL to Relational Algebra
SQL Server:Select Rows Who's Sum Matches a Value
Update Table with Random Record in Update Statment in SQL Server
Convert Nvarchar to Datetime in SQL Server 2008
What's the Most Efficient Way to Normalize Text from Column into a Table
Timezone Date Format in Oracle
Whats Wrong with This SQL Statement for Table Variable Bulk Insert
Distinct Listagg That Is Inside a Subquery in the Select List
Postgres Won't Accept Table Alias Before Column Name
Calculate the Last Day of the Prior Quarter
SQL Query to Select the 'Next' Record (Similar to First or Top N)
Create View Must Be the Only Statement in the Batch
How to Insert Random Values into a SQL Server Table
MySQL Datetime Group by 15 Mins
Incorrect Parameter Count in the Call to Native Function 'Datediff'