Add a Row Number to Result Set of a SQL Query

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 generate sequential row number in tsql?

There is no need to avoid Analytic Functions if your database supports them e.g ROW_NUMBER()

    SELECT
        ROW_NUMBER() OVER (ORDER BY [<PRIMARYKEY_COLUMN_NAME>]) AS Number
    FROM
        [<TABLE_NAME>]

The syntax is Func([ arguments ]) OVER (analytic_clause) you need to focus on OVER (). This last parentheses make partition(s) of your rows and apply the Func() on these partitions one by one. In above code we have only single set/partition of rows. Therefore the generated sequence is for all the rows.

You can make multiple set of your data and generate sequence number for each one in a single go. For example if you need generate sequence number for all the set of rows those have same categoryId. You just need to add Partition By clause like this (PARTITION BY categoryId ORDER BY [<PRIMARYKEY_COLUMN_NAME>]).

Remember that after FROM you can also use another extra ORDER BY to sort your data differently. But it has no effect on the OVER ()

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 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

Include row number in query result (SQL Server)

If you are referring to the row number provided by Management Studio when you run a query, there is no way to get that because it does not really exist. Management Studio generates that on the fly. You can however, recreate a sequential number using the ROW_NUMBER ranking function if you are using SQL Server 2005 or later. Note you should never assume the database will return the rows in a specified order unless you include an Order By statement. So your query might look like:

Select ....
    , Row_Number() Over ( Order By T.SomeColumn ) As Num
From Table As T
Order By T.SomeColumn

The Order By in the Over clause is used to determine the order for creating the sequential numbers. The Order By clause at the end of the query is used to determine the order of the rows in the output (i.e. the order for the sequence number and the order of the rows can be different).

SQL Server Add row number each group

You can assign groups to adjacent pay types that are the same and then use row_number(). For this purpose, the difference of row numbers is a good way to determine the groups:

select row_number() over (partition by pay_type, seqnum - seqnum_2 order by serial_no) as row_no,
       t.*
from (select t.*,
             row_number() over (order by serial_no) as seqnum,
             row_number() over (partition by pay_type order by serial_no) as seqnum_2
      from t
     ) t;

This type of problem is one example of a gaps-and-islands problem. Why does the difference of row numbers work? I find that the simplest way to understand is to look at the results of the subquery.

Here is a db<>fiddle.

---

Related Topics