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

split row_number() over partition over multiple columns

I have a query which uses row_number() over partition. When the result comes out it looks like Product Row_Number Price A 1 25 A 2 20 A 3 …
John Henry
  • 165
  • 1
  • 4
  • 14
4
votes
2 answers

MS Access alternative to SQL function ROW_NUMBER()

I work with MS Access 07 and need a little help. I have two tables TbProjectTeam and TbProjectList. I need to compare date of employee turn-out and date of project start. I used that SQL syntax: SELECT [TbProjectTeam ].[Surname, name] FROM…
Zdenek T.
  • 65
  • 2
  • 9
4
votes
1 answer

Obtain maximum row_number inside a cross apply

I am having trouble in calculating the maximum of a row_number in my sql case. I will explain it directly on the SQL Fiddle example, as I think it will be faster to understand: SQL Fiddle Columns 'OrderNumber', 'HourMinute' and 'Code' are just to…
Alfons
  • 147
  • 2
  • 10
4
votes
5 answers

Should SQL ranking functionality be considered as "use with caution"

This question originates from a discussion on whether to use SQL ranking functionality or not in a particular case. Any common RDBMS includes some ranking functionality, i.e. its query language has elements like TOP n ... ORDER BY key, ROW_NUMBER()…
The Chairman
  • 7,087
  • 2
  • 36
  • 44
4
votes
3 answers

SQL Server rownumbering and filtered results

I have an application in which I need to display "pages" of data in a list. The basic idea is fairly common. The display shows a list of items and at the bottom of the display are some kind of controls that allow you to go to the next "page" of…
horace
  • 938
  • 9
  • 20
4
votes
2 answers

how to set column value equal to row no?

How can i set value of column that has been added after altering table equal to row no in sql server 2008. That is i want value of the column equal to no. of row. I also want this field to allow NULL values. So it is like auto increment but allowing…
user13
  • 85
  • 1
  • 1
  • 9
4
votes
3 answers

Nhibernate row number over partition c#

I an new in nhibernate and need your help. How can I execute this sql query in nhibernate-criteria C# select * from ( select *, row_number() over (partition by questionaireId order by stepnumber desc) rid from myTable ) t where t.rid=1
zina
  • 258
  • 2
  • 9
3
votes
5 answers

SQL Query With Row_Number, order by and where clause

I have the following SQL query: select      ID, COLUMN1, COLUMN2 from      (select ID, COLUMN1, COLUMN2, row_number() over (order by 2 DESC) NO from A_TABLE) where      NO between 0 and 100 What I am trying to do is to select the first 100 records…
Bogdan M
  • 145
  • 1
  • 1
  • 8
3
votes
1 answer

MySQL - Select row number of a record

I have a table in MySQL populated as follows. Now I need to select the row number of a record in its sorted order. For example, the row number of words starting with 'c' should be 4. Words ===== coffee banana apple cherry blackberry I tried the…
Vivek
  • 4,526
  • 17
  • 56
  • 69
3
votes
3 answers

Add a sequential number on create / insert - Teradata

In oracle we would use rownum on the select as we created this table. Now in teradata, I can't seem to get it to work. There isn't a column that I can sort on and have unique values (lots of duplication) unless I use 3 columns together. The old way…
AFHood
  • 1,020
  • 4
  • 15
  • 26
3
votes
3 answers

Count if a value appears in consecutive rows

I've got this table: ID Date Event ---------------------------------------- 123 2022-05-01 OCT 123 2022-05-04 OCT 123 2022-05-05 OCT 123 2022-05-07 OCT …
KapSht
  • 157
  • 8
3
votes
1 answer

ROW_NUMBER function does not start from 1

I would like to ask about strange behaviour in SQL Server whilst using ROW_NUMBER() Function. Typically it should start from 1 and Order values by the selected column in Order By clause, which for the most scenarios works for me just as it is…
Calle
  • 151
  • 1
  • 7
3
votes
1 answer

How to overwrite a row_number value in SQL?

How to overwrite a column in SQL (Redshift) ? The following query gives me an error Target table must be part of an equijoin predicate UPDATE table1 SET rank = temp.new_rank FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY session_id ORDER BY…
Corentin Duhamel
  • 221
  • 3
  • 11
3
votes
4 answers

SQL row_number() - how to ignore nulls?

I need to add counters to users activity, using this query: select PERSON_ID, TIMESTAMP, row_number() over (partition by PERSON_ID order by TIMESTAMP asc) as PERSON_COUNTER from table1; This works well, but it counts also the case where…
Ezer K
  • 3,637
  • 3
  • 18
  • 34
3
votes
1 answer

MYSQL - Warning: #1287 Setting user variables within expressions is deprecated and will be removed in a future release

Warning: #1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'. This is what MySQL 8.0.21 spits out.…
digitalniweb
  • 838
  • 1
  • 7
  • 15
1 2 3
…
80 81