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
27
votes
1 answer

Row numbering in PostgreSQL

How to get row number in PostgreSQL when the results are ordered by some column? e.g. SELECT 30+row_number() AS position, * FROM users ORDER BY salary DESC LIMIT 30 OFFSET 30 I supposed that the query would return list like this: position |…
Radek Simko
  • 15,886
  • 17
  • 69
  • 107
27
votes
4 answers

How do I get a SQL row_number equivalent for a Spark RDD?

I need to generate a full list of row_numbers for a data table with many columns. In SQL, this would look like this: select key_value, col1, col2, col3, row_number() over (partition by key_value order by col1, col2 desc, col3) from …
Glenn Strycker
  • 4,816
  • 6
  • 31
  • 51
26
votes
5 answers

In an Oracle database, what is the difference between ROWNUM and ROW_NUMBER?

What is the difference between ROWNUM and ROW_NUMBER ?
Bob Dizzle
  • 1,173
  • 1
  • 8
  • 18
21
votes
2 answers

Creating a row number of each row in PySpark DataFrame using row_number() function with Spark version 2.2

I am having a PySpark DataFrame - valuesCol = [('Sweden',31),('Norway',62),('Iceland',13),('Finland',24),('Denmark',52)] df = sqlContext.createDataFrame(valuesCol,['name','id']) +-------+---+ | name| id| +-------+---+ | Sweden| 31| | Norway|…
cph_sto
  • 7,189
  • 12
  • 42
  • 78
20
votes
3 answers

Trouble using ROW_NUMBER() OVER (PARTITION BY ...)

I'm using SQL Server 2008 R2. I have table called EmployeeHistory with the following structure and sample data: EmployeeID Date DepartmentID SupervisorID 10001 20130101 001 10009 10001 20130909 001 10019 10001 …
Thracian
  • 651
  • 4
  • 8
  • 24
20
votes
3 answers

How to add row number column in SQL Server 2012

I'm trying to add a new column to an existing table, where the value is the row number/rank. I need a way to generate the row number/rank value, and I also need to limit the rows affected--in this case, the presence of a substring within a…
Michael Carper
  • 331
  • 2
  • 4
  • 10
18
votes
2 answers

Sequentially number rows by keyed group in SQL?

Is there a way in SQL to sequentially add a row number by key group? Assume a table with arbitrary (CODE,NAME) tuples. Example table: CODE NAME ---- ---- A Apple A Angel A Arizona B Bravo C Charlie C Cat D Dog D …
Jé Queue
  • 10,359
  • 13
  • 53
  • 61
18
votes
2 answers

Is it possible to use ROW_NUMBER() OVER/PARTITION BY only when another column is also the same?

I am using this code: (from this question: How to get the last record per group in SQL substituting my own columns) WITH e AS ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY ApplicationId ORDER BY theDate DESC ) AS…
tsdexter
  • 2,911
  • 4
  • 36
  • 59
16
votes
1 answer

PostgreSQL window function: row_number() over (partition col order by col2)

Following result set is derived from a sql query with a few joins and a union. The sql query already groups rows on Date and game. I need a column to describe the number of attempts at a game partitioned by date column. Username Game ID …
user1951677
  • 329
  • 1
  • 3
  • 11
16
votes
3 answers

How to show row number in Access query like ROW_NUMBER in SQL

I have a table in Microsoft Access, and I want to show row number in a column using a select query in Access just like using ROW_NUMBER() function in SQL Server. In SQL Server, I can using this query: SELECT ROW_NUMBER() OVER (ORDER BY…
Fauzi88
  • 713
  • 3
  • 11
  • 23
15
votes
6 answers

Getting the First and Last Row Using ROW_NUMBER and PARTITION BY

Sample Input Name | Value | Timestamp -----|-------|----------------- One | 1 | 2016-01-01 02:00 Two | 3 | 2016-01-01 03:00 One | 2 | 2016-01-02 02:00 Two | 4 | 2016-01-03 04:00 Desired Output Name | Value | EarliestTimestamp |…
Muhammad Rehan Saeed
  • 35,627
  • 39
  • 202
  • 311
14
votes
2 answers

filter by row_number in sqlalchemy

how can I filter the row_number ==1 in the following query: query = session.query(Foo, func.row_number().over( partition_by=Foo.foo_field, order_by=desc(Foo.foo_date_time)).label("row_number")) query = query.filter(Foo.time_key <=…
Mehrdad
  • 2,054
  • 3
  • 20
  • 34
14
votes
1 answer

How to show row count in SSRS per grouping on variable group-by value?

Essentially, I'd like to have separate row counts per grouping. I believe I'm supposed to use RowNumber. This seems to have been discussed but I'm having difficulty due to the fact that my groupby is not a string constant. My attempts have lead to…
D'Arcy Rail-Ip
  • 11,505
  • 11
  • 42
  • 67
12
votes
1 answer

Why do Recursive CTEs run analytic functions (ROW_NUMBER) procedurally?

I answered a recursive CTE yesterday that exposed an issue with the way that these are implemented in SQL Server (possibly in other RDBMS, too?). Basically, when I try to use ROW_NUMBER against the current recursive level, it runs against each row…
Justin Pihony
  • 66,056
  • 18
  • 147
  • 180
12
votes
5 answers

How can I speed up row_number in Oracle?

I have a SQL query that looks something like this: SELECT * FROM( SELECT ..., row_number() OVER(ORDER BY ID) rn FROM ... ) WHERE rn between :start and :end Essentially, it's the ORDER BY part that's slowing things…
Jason Baker
  • 192,085
  • 135
  • 376
  • 510
1
2
3
…
80 81