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

How to filter on ROW_NUMBER()

I am trying to select distinct NAME from a dataset but also return other columns. I have it working to a degree but just cant figure out how to bring it together. I suspect I need a WITH x( or something but am unsure Here is the CODE and an image…
Orin Moyer
  • 509
  • 2
  • 7
  • 13
8
votes
2 answers

SQL Transpose rows to columns (group by key variable)?

I am trying to transpose rows into columns, grouping by a unique identifier (CASE_ID). I have a table with this structure: CASE_ID AMOUNT TYPE 100 10 A 100 50 B 100 75 A 200 33 B …
user2895991
  • 101
  • 1
  • 1
  • 4
8
votes
1 answer

increment row number when value of field changes in Oracle

I need help in writing a query in Oracle for the following data. The data is sorted by Person and Day fields. Person Day Flag ------ --- ---- person1 day1 Y person1 day2 Y person1 day3 Y person1 …
pravi
  • 175
  • 1
  • 2
  • 7
8
votes
4 answers

LINQ-to-objects index within a group + for different groupings (aka ROW_NUMBER with PARTITION BY equivalent)

After much Google searching and code experimentation, I'm stumped on a complex C# LINQ-to-objects problem which in SQL would be easy to solve with a pair of ROW_NUMBER()...PARTITION BY functions and a subquery or two. Here's, in words, what I'm…
Justin Grant
  • 44,807
  • 15
  • 124
  • 208
7
votes
1 answer

Row_Number over (partition by...) all columns

I have a view with something like 150 columns and I want to add an Id column to that view. Is it possible not to write all the column names in the over (partition by... ) statment? something like this: row_number over (partition by *) As ID?
Toto88
  • 129
  • 3
  • 11
7
votes
2 answers

regexp_split_to_table and row_number

I have table with string data like this: id | string_data 1 | red;green;blue 2 | orange 3 | purple;cyan And I need split string data to items with row numbers: id | num | item 1 | 1 | red 1 | 2 | green 1 | 3 | blue 2 | 1 | orange 3 …
Cateh
  • 71
  • 1
  • 1
  • 2
7
votes
3 answers

Dense Rank with order by

I have Assignment Table like this EMPLID | RCD | COMPANY | EFFDT | SALARY --------------------------------------------------- 100 | 0 | xyz | 1/1/2000 | 1000 100 | 0 | xyz | 1/15/2000 | 1100 100 | 0 |…
Bhushan
  • 115
  • 7
7
votes
3 answers

Update a column based on row_number()

i created a new not-null column with default value 0 for my table and it keeps display orders. I want to update all rows for that table, that displayorder has the value of row_number() over id ordered. here i can do this for one id. How can i do…
Can
  • 659
  • 1
  • 7
  • 24
7
votes
3 answers

SQL ROW_NUMBER gives error

I need to order rows in MySQL and assign a number to each row according to that order. ORDER BY is working as intended but not ROW_NUMBER(). This works: USE my_database; SELECT id ,volume FROM my_table ORDER BY volume; This does not…
anvandarnamn
  • 133
  • 1
  • 2
  • 8
7
votes
3 answers

Row_Number() in Access select statement

I believe similar questions have been asked but I can't quite find a solution that works for me. I've got a database that I use to sort through digitised books and their pages and I'm trying to sort through several thousand pages that contain maps.…
user2770656
  • 71
  • 1
  • 1
  • 2
7
votes
2 answers

t-SQL to find Top 10 Records for Each Group

I am trying to figure out how to return the top 10 records for each group of Trans.TranSID. SELECT a.ABID, a.ABName, t.TranSID, SUM(IIF(TranTypeID = 'CO', td.Qty * CAST(td.Price AS money) * - 1, td.Qty * CAST(td.Price AS…
GJGerson
  • 211
  • 1
  • 9
  • 20
7
votes
1 answer

How to add ROW_NUMBER() in a view?

In PostgreSQL 8.4 I want to create a view from 3 tables with id. So I want to have this structure in my view: num serial, name_dispatcher character varying(250) the_geom geometry I can select name_dispatcher and the_geom from tables: CREATE VIEW…
Kliver Max
  • 5,107
  • 22
  • 95
  • 148
7
votes
3 answers

ROW_NUMBER with partition by returns result with duplicate rows

When I run this query I get more rows than from a similar query without the ROW_NUMBER () line: SELECT DISTINCT id, value, ROW_NUMBER () OVER (PARTITION BY (id) ORDER BY value DESC NULLS LAST ) max FROM TABLE1…
user1658192
  • 221
  • 1
  • 4
  • 10
6
votes
3 answers

how to avoid duplicate on Joining two tables

Student Table SID Name 1 A 2 B 3 C Marks Table id mark subject 1 50 physics 2 40 biology 1 50 chemistry 3 30 mathematics SELECT distinct(std.id),std.name,m.mark, row_number() over() as rownum FROM…
zod
  • 12,092
  • 24
  • 70
  • 106
6
votes
1 answer

Does Android Room Database Query support row_number?

I am trying to get the following sqlite query working in my android room database, but it's complaining with a couple of errors. @Query("with cte as (\n" + " select t.exercise_name,t.band, t.reps, t.weight\n" + " from ( \n" + …
murock
  • 73
  • 7
1 2 3
…
80 81