Assign Row number to the result set without using Row_Number

Question

Post reply

Assign Row number to the result set without using Row_Number

Priya004

Ten Centuries

Points: 1368
More actions
August 14, 2014 at 3:38 am

#292226

Hi,
Is there any other way to assign row number to the result set with out using Row_Number/rank/Dense rank functions or loops?
Thanks,
Indu

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

You could create a temp table with an identity column, insert the data and then query it back.

Why the restriction against the obvious solution (row number)?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 2

Indu-649576 (8/14/2014)
Hi,
Is there any other way to assign row number to the result set with out using Row_Number/rank/Dense rank functions or loops?
Thanks,
Indu

While we are making things more complicated, you could write a SQL CLR function that assigns a row number to your result set...

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Priya004 Ten Centuries Points: 1368 More actions · Answer 3

I really don't understand the CLR /CTE concepts ..somebody ..plz explain

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

CLR - Common Language Runtime. .net code in SQL Server. You can create procedures and functions which are written in .Net.

Koen's suggestion wasn't serious (I nope) and besides would violate your 'no loops' requirement I suspect.

A CTE is a common table expression, a named subquery. No one mentioned or suggested CTEs here.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 5

Indu-649576 (8/14/2014)
I really don't understand the CLR /CTE concepts ..somebody ..plz explain

It was an attempt to a joke. Don't use CLR unless you like being miserable.

Go with the temp table/identity solution. Or just use ROW_NUMBER, like everybody else.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 6

GilaMonster (8/14/2014)
Koen's suggestion wasn't serious (I nope) and besides would violate your 'no loops' requirement I suspect.

I was not serious 😀

This is serious though:

- add a column with only the value 1 in it

- create a running total on this column.

You can use the quirky update method laid out by Jeff Moden in this article: Solving the Running Total and Ordinal Rank Problems (Rewritten)[/url].

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Priya004 Ten Centuries Points: 1368 More actions · Answer 7

I really don't like this temp table creation as in my code I have to use this logic of row number multiple times..

Tell me if there are any other ideas

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

You still haven't explained why the obvious solution, ROW_NUMBER, isn't allowed.

If I needed row numbers in any version of SQL above 2008, I'd use ROW_NUMBER(). If I needed row numbers in SQL 2000 or earlier, I'd use the temp table with identity option.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Koen Verbeeck (8/14/2014)
- add a column with only the value 1 in it
- create a running total on this column.
You can use the quirky update method laid out by Jeff Moden

Yeah, but doesn't that need a temp table?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 10

GilaMonster (8/14/2014)
Koen Verbeeck (8/14/2014)
- add a column with only the value 1 in it
- create a running total on this column.
You can use the quirky update method laid out by Jeff Moden
Yeah, but doesn't that need a temp table?

Probably yes. I didn't know about the aversion of temp tables when I was writing my reply.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 11

Indu-649576 (8/14/2014)
Hi,
Is there any other way to assign row number to the result set with out using Row_Number/rank/Dense rank functions or loops?
Thanks,
Indu

Yes... there is. How many rows in the result set and how bad can performance be allowed to get?

While you're at it, please answer the question that every has asked you. Why do you want to or need to avoid the windowing functions? Your answer could actually make a difference here.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 12

You can try out these methods as previously suggested, hard to understand why going through all the trouble instead of using ROW_NUMBER!

😎

USE tempdb;

GO

SET NOCOUNT ON;

DECLARE @RESSET TABLE

(

RS_NUM INT NOT NULL

,RS_NAME VARCHAR(20) NOT NULL

,RS_FLAG CHAR(1) NOT NULL

,RS_DATE DATETIME NOT NULL

);

INSERT INTO @RESSET

(

RS_NUM

,RS_NAME

,RS_FLAG

,RS_DATE

)

SELECT TOP 50

SO.object_id + 15000000

,SO.name

,SO.type

,SO.create_date

FROM sys.objects SO

WHERE SO.object_id > 0;

/* TEMP TABLE WITH IDENTITY */

CREATE TABLE #RN_RESULTS1

(

MY_OWN_RN INT IDENTITY(1,1) NOT NULL

,RS_NUM INT NOT NULL

,RS_NAME VARCHAR(20) NOT NULL

,RS_FLAG CHAR(1) NOT NULL

,RS_DATE DATETIME NOT NULL

);

INSERT INTO #RN_RESULTS1

(

RS_NUM

,RS_NAME

,RS_FLAG

,RS_DATE

)

SELECT

RS_NUM

,RS_NAME

,RS_FLAG

,RS_DATE

FROM @RESSET

SELECT

*

FROM #RN_RESULTS1;

DROP TABLE #RN_RESULTS1;

/* TEMP TABLE AND A CURSOR */

CREATE TABLE #RN_RESULTS2

(

MY_OWN_RN INT NOT NULL

,RS_NUM INT NOT NULL

,RS_NAME VARCHAR(20) NOT NULL

,RS_FLAG CHAR(1) NOT NULL

,RS_DATE DATETIME NOT NULL

);

DECLARE @MY_OWN_RN INT ;

DECLARE @RS_NUM INT ;

DECLARE @RS_NAME VARCHAR(20);

DECLARE @RS_FLAG CHAR(1) ;

DECLARE @RS_DATE DATETIME ;

SET @MY_OWN_RN = 0;

DECLARE R_SET CURSOR FAST_FORWARD FOR

SELECT

RS_NUM

,RS_NAME

,RS_FLAG

,RS_DATE

FROM @RESSET RS;

OPEN R_SET;

FETCH NEXT FROM R_SET INTO @RS_NUM, @RS_NAME, @RS_FLAG, @RS_DATE;

WHILE @@FETCH_STATUS = 0

BEGIN

SET @MY_OWN_RN = @MY_OWN_RN + 1;

INSERT INTO #RN_RESULTS2

(

MY_OWN_RN

,RS_NUM

,RS_NAME

,RS_FLAG

,RS_DATE

)

VALUES (@MY_OWN_RN,@RS_NUM, @RS_NAME, @RS_FLAG, @RS_DATE)

FETCH NEXT FROM R_SET INTO @RS_NUM, @RS_NAME, @RS_FLAG, @RS_DATE;

END

CLOSE R_SET

DEALLOCATE R_SET

SELECT

MY_OWN_RN

,RS_NUM

,RS_NAME

,RS_FLAG

,RS_DATE

FROM #RN_RESULTS2;

DROP TABLE #RN_RESULTS2;

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

Eirikur Eiriksson (8/14/2014)
You can try out these methods as previously suggested

However OP said

I really don't like this temp table creation

So, no row number, no temp tables. Really limits the options.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 14

GilaMonster (8/15/2014)
Eirikur Eiriksson (8/14/2014)
You can try out these methods as previously suggested
However OP said
I really don't like this temp table creation
So, no row number, no temp tables. Really limits the options.

And no loops!

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP