Stack Overflow
Stack Overflow

Questions tagged [row-number]

An index or rank which indicates a row's position in a data-set.

When referring to multiple rows of information, generally drawn from a database, the row-number refers to the ordinal position at which a specific row of information exists within a given data-set.

Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

Row number function implementations exist in multiple SQL variants, such as Oracle, TSQL and MySQL.

ROW_NUMBER is an analytic function. It assigns a unique number to each row to which it is applied (either each row in the partition or each row returned by the query), in the ordered sequence of rows specified in the order_by_clause, beginning with 1.

1205 questions
331
votes
27 answers

ROW_NUMBER() in MySQL

Is there a nice way in MySQL to replicate the SQL Server function ROW_NUMBER()? For example: SELECT col1, col2, ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY col3 DESC) AS intRow FROM Table1 Then I could, for example, add a…
Paul
  • 16,285
  • 13
  • 41
  • 52
214
votes
13 answers

How do I use ROW_NUMBER()?

I want to use the ROW_NUMBER() to get... To get the max(ROW_NUMBER()) --> Or i guess this would also be the count of all rows I tried doing: SELECT max(ROW_NUMBER() OVER(ORDER BY UserId)) FROM Users but it didn't seem to work... To get…
Matt
203
votes
6 answers

MySQL - Get row number on select

Can I run a select statement and get the row number if the items are sorted? I have a table like this: mysql> describe orders; +-------------+---------------------+------+-----+---------+----------------+ | Field | Type | Null |…
George
  • 3,251
  • 7
  • 32
  • 39
101
votes
13 answers

Equivalent of Oracle's RowID in SQL Server

What's the equivalent of Oracle's RowID in SQL Server?
None
96
votes
8 answers

Rownum in postgresql

Is there any way to simulate rownum in postgresql ?
johnlemon
  • 20,761
  • 42
  • 119
  • 178
64
votes
7 answers

Add numbers to the beginning of every line in a file

How can I add numbers to the beginning of every line in a file? E.g.: This is the text from the file. Becomes: 000000001 This is 000000002 the text 000000003 from the file.
Village
  • 22,513
  • 46
  • 122
  • 163
58
votes
2 answers

Why is there still a row limit in Microsoft Excel?

Until Office 2007, Excel has a maximum of 65,000 rows. Office 2007 bumped that up to a max of 1 million rows, which is nicer of course; but I'm curious -- why is there a limit at all? Obviously, performance will slow down exponetially as you…
D-Dog
55
votes
4 answers

Oracle 'Partition By' and 'Row_Number' keyword

I have a SQL query written by someone else and I'm trying to figure out what it does. Can someone please explain what the Partition By and Row_Number keywords does here and give a simple example of it in action, as well as why one would want to use…
HashimR
  • 3,803
  • 8
  • 32
  • 49
54
votes
4 answers

Find Cell Matching Value And Return Rownumber

The employee sheet contains the name of the employee in cell C2. The name of the employee should also be on the data sheet in the range B3:B153. How can I get the rownumber of the cell on the data sheet that matches the employee name? I tried the…
LennartB
  • 559
  • 1
  • 5
  • 6
53
votes
4 answers

MSSQL Select statement with incremental integer column... not from a table

I need, if possible, a t-sql query that, returning the values from an arbitrary table, also returns a incremental integer column with value = 1 for the first row, 2 for the second, and so on. This column does not actually resides in any table, and…
Rodrigo
  • 4,365
  • 3
  • 31
  • 49
53
votes
2 answers

row_number() Group by?

I have a data set that contains the columns Date, Cat, and QTY. What I want to do is add a unique column that only counts unique Cat values when it does the row count. This is what I want my result set to look like: By using the SQL query below,…
user1582928
  • 535
  • 1
  • 5
  • 5
45
votes
6 answers

Select specific row from mysql table

Ideally I need a query that is equivalent to select * from customer where row_number() = 3 but that's illegal. I can't use an auto incremented field. row_number() is the row that needs to be selected. How do I go about this? EDIT: Well, I use…
Nu Gnoj Mik
  • 994
  • 3
  • 10
  • 25
44
votes
6 answers

How to use ROW_NUMBER in sqlite

Here is my query given below. select * from data where value = "yes"; My id is auto increment and below there is result of given query. id || value 1 || yes 3 || yes 4 || yes 6 || yes 9 || yes How to use ROW_NUMBER in sqlite? So…
Puja Surya
  • 1,155
  • 4
  • 20
  • 47
39
votes
5 answers

Manually specify starting value for Row_Number()

I want to define the start of ROW_NUMBER() as 3258170 instead of 1. I am using the following SQL query SELECT ROW_NUMBER() over(order by (select 3258170)) as 'idd'. However, the above query is not working. When I say not working I mean its…
Huzaifa
  • 1,111
  • 5
  • 20
  • 37
35
votes
5 answers

How To Project a Line Number Into Linq Query Results

How can I project the row number onto the linq query result set. Instead of say: field1, field2, field3 field1, field2, field3 I would like: 1, field1, field2, field3 2, field1, field2, field3 Here is my attempt at this: public List…
Jeff Weber
  • 949
  • 1
  • 8
  • 18
1
2 3
…
80 81