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
5
votes
2 answers

SQL Server - group by with row number - Gaps and Islands

I have a table with data like this +-------------+--------------+------------+----------------+ | CustomerSID | StartDateSID | EndDateSID | MarketingOptIn | +-------------+--------------+------------+----------------+ | 12345 | 20101019 | …
Rohit P
  • 613
  • 5
  • 13
5
votes
2 answers

SQL Server 2012: Conditionally Incrementing a counter user ROW_NUMBER()

I am trying to apply ROW_NUMBER() to increment a counter based on particular conditions. My data looks like this, with the target counter being the Prep column id DSR PrepIndicator Prep -------------------------------------- …
Shnita Moluna
  • 63
  • 2
  • 4
5
votes
1 answer

Need a MySQL query that can filter the results

Here, the chapter_id can be two or more than two. I need to query in a way that every chapter give the output of equal no. of questions. i.e. If the total question is 50, and from 5 chapters, then each chapter should give 10 questions. SELECT id,…
TheManish
  • 183
  • 1
  • 15
5
votes
2 answers

Is it possible to add a identity to a GROUP BY using SQL?

Is it possible to add a identity column to a GROUP BY so that each duplicate has a identity number? My original data looks like this: 1 AAA [timestamp] 2 AAA [timestamp] 3 BBB [timestamp] 4 CCC [timestamp] 5 CCC [timestamp] 6 …
djangofan
  • 28,471
  • 61
  • 196
  • 289
5
votes
1 answer

Dynamically numbering distinct sql rows in select statement

How would I dynamically number rows in a query similar to this: Select distinct name from @table where %rules When I add ROW_NUMBER() OVER() I lose my distinct property, and it returns every element in table, with a unique row number. Select…
Subliminy
  • 364
  • 4
  • 15
5
votes
7 answers

Row numbers for a query in informix

I am using informix database, I want a query which you could also generate a row number along with the query Like select row_number(),firstName,lastName from students; row_number() firstName lastName 1 john mathew 2 …
shivakumar
5
votes
2 answers

SQL Server: row_number partitioned by timeout

I have a table with a series of (IP varchar(15), DateTime datetime2) values. Each row corresponds to an HTTP request made by a user. I want to assign session numbers to these rows. Different IP-addresses have different session numbers. The same IP…
usr
  • 168,620
  • 35
  • 240
  • 369
4
votes
4 answers

Dynamic order by when using order by - speeding up

I am using row number to get paged results back from a stored procedure. I am finding that ordering using a dynamic case statement column name is slowing things down - but if I hardcode the order by everything is ok. Is there a way to speed up the…
AJM
  • 32,054
  • 48
  • 155
  • 243
4
votes
1 answer

Row_Number with complex conditions

Is it possible to use Row_Number() to number rows on something else than a simple partition done with Group By ? This is my particular case : Id Type Date -- ---- ---- 1 16 Some Date 2 16 Some Date 3 16 Some…
krtek
  • 26,334
  • 5
  • 56
  • 84
4
votes
2 answers

How to update a column with unique serial number depending on two different columns of the same rows in PostgreSQL?

I have a list of records in the below format (example) produced by joining multiple tables from the database and where…
Sunil Cyriac
  • 413
  • 4
  • 16
4
votes
3 answers

How to use row_number() in SQL Server

I want to update row data where the row_number of the column (p_id) is 1.. but this syntax is providing error: update app1 set p_id = 1 where Row_Number() = 1 over(p_id)
user635545
  • 111
  • 1
  • 4
  • 12
4
votes
2 answers

How to get the detail row number for rows in JSON string

I have a stored procedure that accepts a NVARCHAR(max) string that is JSON data that I need to validate before loading it into the live tables. If the validation fails I need to return a message with the issue and the row number of the bad data row.…
Keith Miller
  • 115
  • 5
  • 11
4
votes
2 answers

How can I use MySQL ROW_NUMBER Function in a query

I am trying to learn about the MySQL ROW_NUMBER() function and how to use it to generate a sequential number for each row in a result set. What I tried so far: SELECT e.*, ROW_NUMBER() OVER(PARTITION BY e.examid ORDER BY e.examid) AS id from exam…
Olajumoke Ademilade
  • 191
  • 1
  • 14
4
votes
2 answers

I want to generate continuously number by 2 column and batch wise

I want to generate continuously number with the combination of 2 columns and in batch size of 5. Anybody can help to solve this?
Paras Golakiya
  • 51
  • 4
4
votes
3 answers

ROW_NUMBER() to be applied only for top N records

I have a scenario to get the zero-indexed value for a result set based on the Id column. I can achieve this by ROW_NUMBER(). But there was a slight change in the expected behavior now. The expectation is, for the top five records only the…
Arulkumar
  • 12,966
  • 14
  • 47
  • 68
1 2 3
…
80 81