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

ROW_NUMBER vs COUNT(1)?

Which of the two alternatives is better? ROW_NUMBER() OVER (PARTITION BY...) or COUNT(1) OVER (PARTITION BY ...) I could not find any such question. Edit: DBMS: SQL-SERVER (version >= 2008) In my case the over partition is guaranteed by a single…
Daniele Palazzo
  • 41
  • 1
  • 5
4
votes
1 answer

row_number() gets wrong result in certain conditions

I have a SQL query that works perfectly in SQL Server, but it fails on Oracle and, in my opinion, it shouldn't. This is the example to reproduce it: CREATE TABLE TEST ( TEST_ID NUMBER(37,0) NOT NULL, TEST_NAME VARCHAR2(50 BYTE), …
Matias
  • 53
  • 1
  • 4
4
votes
2 answers

Mysql get row number

I've a table like this +------+----------+------------+ | id | 1_value | 2_value | +------+----------+------------+ | 3 | foo1 | other | | 10 | fooX | stuff | | 13 | fooJ | here | | 22 | foo7 | and …
Dudo1985
  • 177
  • 2
  • 3
  • 12
4
votes
1 answer

TSql Return column based on partition and rownumber

I have a SQL server table where I'm attempting to get a computed column - MyPartition - indicating the number of the partition based on a variable @segment. For example, if @segment = 3 then the following output would be true. RowID | RowName |…
Breaker
  • 319
  • 2
  • 11
4
votes
4 answers

How to print row number in mysql in PHP application

I have a mysql table need to display the data along with the row number in front end aplication. The following query works perfectly in phpMyadmin SET @row_num=0; SELECT (@row_num:=@row_num+1) AS num,INV,DOS,PTNAME,BAL,PROV from sheet; But when…
manikandan
  • 139
  • 1
  • 2
  • 15
4
votes
2 answers

Consecutive Count on Record Result

I'm working on a data structure with list of positive or negative result for each person. Sample data (id is an identity): id person result 1 1 0 2 1 1 3 1 1 4 2 1 5 2 0 6 1 …
ydoow
  • 2,969
  • 4
  • 24
  • 40
4
votes
1 answer

RANK or ROW_NUMBER in BigQuery over a large dataset

I need to add row numbers to a large (ca. billion rows) dataset in BigQuery. When I try: SELECT * ROW_NUMBER() OVER (ORDER BY d_arf DESC) plarf FROM [trigram.trigrams8] I get "Resources exceeded during query execution.", because an…
uner
  • 131
  • 1
  • 2
  • 7
4
votes
2 answers

SQL server row-number to tag duplicate rows

I am trying to add a row number to duplicate records. declare @tbl table (col1 varchar(10),col2 varchar(10) ) insert into @tbl values ('a1','b1') insert into @tbl values('a1','b1') insert into @tbl values ('a1','b2') insert into @tbl …
Senthilkumar Elangovan
  • 675
  • 6
  • 14
4
votes
1 answer

SQL Server 2012 row_number ASC DESC performance

In a SQL Server 2012 version 11.0.5058 I've a query like this SELECT TOP 30 row_number() OVER (ORDER BY SequentialNumber ASC) AS [row_number], o.Oid, StopAzioni FROM tmpTestPerf O INNER JOIN Stati s on O.Stato = s.Oid WHERE …
DBO
  • 41
  • 1
4
votes
1 answer

find start and stop date for contiguous dates in multiple rows

I'm having a bit of trouble displaying correct data from my table. Im not really sure what to search for either. Im not sure min(column) or max(column) will help me here. Lets see if i can explain my problem. My table contains this data: > Code…
Andreas
  • 45
  • 1
  • 5
4
votes
3 answers

Grouping a concrete number of rows in a SQL query

I want to group data without any specific criteria, just the number of data for each resultant group. I have a table like this: DATE VAL1 VAL2 ------------ ------ ------ 01-01-2013 5 8 01-02-2013 14…
user3012768
  • 41
  • 1
4
votes
3 answers

ROW_NUMBER() and nhibernate - finding an item's page

given a query in the form of an ICriteria object, I would like to use NHibernate (by means of a projection?) to find an element's order, in a manner equivalent to using SELECT ROW_NUMBER() OVER (...) to find a specific item's index in the query. (I…
Yonatan Karni
  • 977
  • 2
  • 13
  • 32
4
votes
3 answers

SQL Server get latest value in table x before date for not distinct rows in table y

I am trying to get the latest PatientData.DateVal and PatientData.DecVal before PatientTreatment.Startdate for each row in PatientTreatment. E.g I want the latest date and value for a specific type of patientdata before each treatment for every…
Doggabyte
  • 43
  • 4
4
votes
2 answers

How to get row number in SQLite?

I've read many articles regarding how to use a row number in SQLite but none of them gave me the answer I need. I know how to select row number using this query: SELECT (SELECT COUNT() FROM table WHERE title < t.title OR (title = t.title AND…
0101
  • 2,697
  • 4
  • 26
  • 34
4
votes
1 answer

Select stmt with ROW_NUMBER() OVER(PARTITION BY...) in Where clause

I must be doing something wrong. I'm trying to use the row_number function to only select the values that have a rownum of 1, so that I always get the latest index. However rownum is not recognized in the where-clause. SELECT fs.docu_id as docID ,…
Tony Clifton
  • 703
  • 3
  • 14
  • 27
1 2 3
…
80 81