<< Happy Holidays! | Home | Command.ExecuteRow() >>

Simple SQL Row Number

Wednesday, January 26, 2005 10:09 AM

Try this:

use Northwind
select (select SUM(1) from Region where RegionID <= reg.RegionID) as 'Row Number', * from Region reg

Feedback

# re: Simple SQL Row Number

Isn't this just a really complicated way of getting the RegionID? 2/24/2005 3:44 PM | Steve

# re: Simple SQL Row Number

This query is not for getting the RegionID. 2/28/2005 2:13 AM | Chris Ongsuco

# re: Simple SQL Row Number

Won’t work if you need to sort records i.e. use ORDER BY 4/9/2005 9:51 PM | Alex

# re: Simple SQL Row Number

Alex,

Yes it wont work if there is an order by clause 4/12/2005 11:06 AM | Chris Ongsuco

# re: Simple SQL Row Number

This Query not work properly..
11/23/2005 4:32 AM | Arun

# re: Simple SQL Row Number

tried it in oracle sql, works only if u have an order by clause on the column which is being compared in <= clause 11/25/2005 2:06 AM | taimur (pakistan)

# re: Simple SQL Row Number

Nice one! helped me out on something specific.
Its a nice way to show people that rownumbering is possible :) 12/16/2005 10:04 AM | Hartfield

# re: Simple SQL Row Number

I agree, brilliant. I adapted this simple example to work with ORDER BY by creating a subquery (and applying SUM(1) to that) with the same order I'm using for the main query. Thanks!
12/23/2005 4:54 PM | Ken

# re: Simple SQL Row Number

Two tables show row number in SQL

select (select sum(1) from (SELECT A.idno FROM table1 A, table2 B where A.idno =B.idno) t2
where t2.idno <= t1.idno ) as rowno, t1.idno, t1.sumxx
from (SELECT A.idno, Sum(A.xx) as sumxx FROM table1 A, table2 B where A.idno =B.idno) t1

Note: idno like RegionID!! 1/5/2006 12:26 PM | yy

# re: Simple SQL Row Number

The last one can be better written in standard SQL as

With Data As
(Select A.idno, Sum(A.xx) As sumxx
From table1 A Inner Join table2 B Using (idno)
Group By A.idno)
Select (Select Sum(1) From table1 A
Inner Join table2 B Using (idno) Where A.idno <= data.idno), data.*
From Data
4/10/2006 6:36 PM | Shan

# re: Simple SQL Row Number

extremely slow for large recordset 2/6/2007 3:02 AM | Fai

# re: Simple SQL Row Number

Excellent .......Thanks 9/21/2007 6:30 AM | Jayapriya C M

# re: Simple SQL Row Number

Bad Bad Bad.. You do realize this turns your N query into a N^2 query, right?

The recommended solution uses the ranking function row_number() and is very efficient for most recordsets.

SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY RegionId ASC) AS Row, * FROM Region) AS Reg

Enjoy! 1/12/2008 12:32 PM | Steve

# re: Simple SQL Row Number

I would do
SELECT ROWNUM, * FROM region

and it can have order by
2/14/2008 9:37 AM | A Rodrigues

# re: Simple SQL Row Number

hi this is gulu call me +91-9872460004 3/25/2008 11:08 PM | gul

# re: Simple SQL Row Number

First record will return NULL because of there is nothing to get sum.. To fix it u can use

select (select COUNT(0) from Region where RegionID <= reg.RegionID) as 'Row Number', * from Region reg

and the first row number will be 0

and if you wanna start it from 1

use this

select (select COUNT(0)+1 from Region where RegionID <= reg.RegionID) as 'Row Number', * from Region reg

thanks.. 4/17/2008 11:10 AM | Tolgahan Albayrak

# re: Simple SQL Row Number

brilliant idea, it worked 4/18/2008 8:00 PM | Jazzi

# re: Simple SQL Row Number

Simply superb..........useful info from you guys 12/18/2008 12:51 AM | hi

# re: Simple SQL Row Number

thank u i used it in paging records thnk u

i love u guys 12/26/2008 3:49 AM | harpreet

# re: Simple SQL Row Number

how can i get individual row
eg: row number =5 2/24/2009 10:07 PM | niza

# re: Simple SQL Row Number

Thanks guys...

even I used ROW_NUMBER in my query and it works simply great. But for robustness, I would like to know the fetched record row numbers i.e If I fetch the record from 20 to 30 row...and SQL returns only 20 - 29 due to some reaspn, I would like pass these info back to client to verify what it has recieved what exactly it asked for . Any idea on how to do this ??

Sampel code :
select * from (select row_number() over(order by([Name])) as RowNum, * from tblMyData) as temptable where RowNum >= 20 and RowNum <= 30 3/29/2009 4:55 PM | Smitha

# re: Simple SQL Row Number

thanks for share. http://taphop.net 10/31/2009 1:11 AM | Jonathan Nguyen

Simple SQL Row Number

Feedback

# re: Simple SQL Row Number

# re: Simple SQL Row Number

# re: Simple SQL Row Number

# re: Simple SQL Row Number

# re: Simple SQL Row Number

# re: Simple SQL Row Number

# re: Simple SQL Row Number

# re: Simple SQL Row Number

# re: Simple SQL Row Number

# re: Simple SQL Row Number

# re: Simple SQL Row Number

# re: Simple SQL Row Number

# re: Simple SQL Row Number

# re: Simple SQL Row Number

# re: Simple SQL Row Number

# re: Simple SQL Row Number

# re: Simple SQL Row Number

# re: Simple SQL Row Number

# re: Simple SQL Row Number

# re: Simple SQL Row Number

# re: Simple SQL Row Number

# re: Simple SQL Row Number

News

Article Categories

Archives

Post Categories

Image Galleries

Architecture & OOP

Blogs I Read - Friends

Blogs I Read - Geeks

O/R Mapper

Resources

Syndication: