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
12
votes
3 answers

CTE, ROW_NUMBER and ROWCOUNT

I am trying to return a page of data and also row count of all data in one stored procedure which looks like following: WITH Props AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY PropertyID) AS RowNumber FROM Property WHERE PropertyType =…
David
  • 830
  • 3
  • 13
  • 25
12
votes
3 answers

Add some kind of row number to a mongodb aggregate command / pipeline

The idea is to return a kind of row number to a mongodb aggregate command/ pipeline. Similar to what we've in an RDBM. It should be a unique number, not important if it matches exactly to a row/number. For a query like: [ { $match: { "author" : {…
ic3
  • 7,917
  • 14
  • 67
  • 115
12
votes
4 answers

T-sql Reset Row number on Field Change

Similar to a recent post of mine "t-sql sequential duration"”, but not exactly the same, I want to reset the row number based on a change in column x (in my case, column "who"). Here's the first query that returns the a small sample of the raw(ish)…
Kris
  • 149
  • 1
  • 1
  • 6
10
votes
1 answer

ROW_NUMBER() OVER () with order by in H2

i'm trying to execute a query on a table in H2 database with ROW_NUMBER clause. Here is my query: SELECT ROW_NUMBER() OVER (order by data), name FROM students But i get an error in H2 console: Syntax error in SQL statement "SELECT ROW_NUMBER()…
Jose Victor
  • 350
  • 1
  • 5
  • 13
10
votes
4 answers

SQL Number - Row_Number() - Allow Repeating Row Number

I'm using SQL Server 2008. I have this data returned in a query that looks pretty much like this ordered by Day and ManualOrder... ID Day ManualOrder Lat Lon 1 Mon 0 36.55 36.55 5 Mon 1 55.55 54.44 …
Andy
  • 1,243
  • 3
  • 22
  • 40
10
votes
2 answers

Conditional RowNumber

I am trying to put conditional numbering depending on a result from RowNum column. When the RowNum is 1 I would like to have new column with brand new increment by 1. In the picture in column RoomNum 5 should be replaced by 2, 9 by 3m 13 by 4, etc.…
Marek
  • 3,555
  • 17
  • 74
  • 123
10
votes
1 answer

SPARK SQL Equivalent of Qualify + Row_number statements

Does anyone know the best way for Apache Spark SQL to achieve the same results as the standard SQL qualify() + rnk or row_number statements? For example: I have a Spark Dataframe called statement_data with 12 monthly records each for 100 unique…
Brian Correro
  • 143
  • 1
  • 1
  • 8
10
votes
1 answer

ROW_NUMBER() equivalent in MySQL for inserting

i'm trying to convert SQL scripts that was created in Microsoft SQL Server to run with a link sever to scripts that can be used in SQL Procedures, the script i'm on uses ROW_NUMBER() OVER(ORDER BY [FIELDS]) to create a primary key that isn't…
Memor-X
  • 2,870
  • 6
  • 33
  • 57
9
votes
4 answers

Generate new line number for each mapping

I need to create a new line number of each unit in my mapping table. Please check the below sample data and expected result. Lines table +--------+------------+------+------+ | FileId | linenumber | code | unit…
Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
9
votes
8 answers

SSRS Row Group + Column Group = RowNumber Issue

I'm back with another SSRS question :-) I'm dealing with survey data. I have a procedure that's returning an organization's response counts per question. So my report is defined as Group on Organization for row and Group on answer for columns. …
Bennett Dill
  • 2,875
  • 4
  • 41
  • 39
9
votes
4 answers

How do to the opposite of "row_number() over (partition by [Col] order by [Col])"

I am trying to combine duplicate entries in a data table and give them a new number. Here is a example data set (runnable copy) declare @tmpTable table (ID Varchar(1), First varchar(4), Last varchar(5), Phone varchar(13), …
Scott Chamberlain
  • 124,994
  • 33
  • 282
  • 431
9
votes
2 answers

Multiple columns in OVER ORDER BY

Is there a way to specify multiple columns in the OVER ORDER BY clause? SELECT ROW_NUMBER() OVER(ORDER BY (A.Col1)) AS ID FROM MyTable A The above works fine, but trying to add a second column does not work. SELECT ROW_NUMBER() OVER(ORDER BY…
jkh
  • 3,618
  • 8
  • 38
  • 66
8
votes
1 answer

PySpark - Add Row Number After Using Explode Function

I'm doing an nlp project and have reviews that contain multiple sentences. I am using the spark-nlp package that outputs one column containing a list of the sentences in each review. I am using explode to create a row for each sentence but I want to…
user3242036
  • 645
  • 1
  • 7
  • 16
8
votes
2 answers

SELECT *, ROW_NUMBER() OVER in Oracle

Given a working example in T-SQL: SELECT *, ROW_NUMBER() OVER (ORDER BY name ) as row_id FROM [schemaName].[Zoo] If I keep * selector it throws following error ORA-00923: FROM keyword not found where expected. Is there a way to keep * selector,…
meta4
  • 788
  • 1
  • 10
  • 24
8
votes
6 answers

Are there any functions in MySQL like dense_rank() and row_number() like Oracle?

Are there any functions in MySQL like dense_rank() and row_number() like those provided by Oracle and other DBMS? I want to generate an id within the query, but in MySQL these functions are not there. Is there an alternative?
CSiva
  • 137
  • 1
  • 1
  • 8
1 2
3
…
80 81