RowNum Function in SQLServer

Question

Post reply

RowNum Function in SQLServer

Dinesh Asanka

SSChampion

Points: 11058
More actions
October 28, 2003 at 1:20 am

#160897

Is there any function in SQL Server like RowNum in Oracle

My Blog: http://dineshasanka.spaces.live.com/

Viewing 13 posts - 1 through 12 (of 12 total)

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

Crispin Proctor SSCoach Points: 18161 More actions · Answer 1

Have to give Frank credit for this!

There was a URL to MS discussing it. Did caution on large tables.

Maybe Frank still has the URL.



Use PUBS

Go

select rank=count(*), a1.au_lname, a1.au_fname

   from authors a1, authors a2

   where a1.au_lname + a1.au_fname >= a2.au_lname + a2.au_fname

   group by a1.au_lname, a1.au_fname

  order by 1

Cheers,

Crispin

Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/q186/1/33.asp&NoWebContent=1

Frank

http://www.insidesql.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Dinesh Asanka SSChampion Points: 11058 More actions · Answer 3

Thank You Both guys

My Blog: http://dineshasanka.spaces.live.com/

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

Somehow the first one seems to be broken I meant

http://support.microsoft.com:80/support/kb/articles/q186/1/33.asp&NoWebContent=1

Frank

http://www.insidesql.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Jonathan SSC-Insane Points: 20427 More actions · Answer 5

As Crispin mentioned, be careful with the set methods when working with large tables, as the performance can degrade remarkably. Also, unless you want "golf tournament" ranking, you will need to include a unique key in the predicate for breaking ties. If this must be done on the server with large tables, it can be faster (as the MS article mentions in passing) to use a temporary table with a "rank" column assigned the identity property.

Personally, I think this sort of thing should be done at a higher tier: return just the ordered result set from SQL Server and use the application language to create the rank numbers.

--Jonathan

tfarren SSC Rookie Points: 39 More actions · Answer 6

The problem with using the dynamic sql methods to number your rows is that the query will take a long time on large tables. Niether will you get unique row identities when you are grouping by fields that contain duplicate values. If you are using SQL Server 2K, A safer and more uniform way to accomplish this is to create a user defined function that inserts your select statement along with an identity value into a table variable. As Jonathan mentioned, I think you will also see a large performance benefit on large tables, and you can employ simpler SQL select statements to achieve your goal. The following is sample code to duplicate the dynamic sql example for the pubs authors table:

Create function dbo.udf_rankauthors

(

)

RETURNS @retAuthors TABLE

(IDX int Identity(1,1),

au_lname varchar(40),

au_fname varchar(20)

)

AS

---- -------------------------------------------------------------------*/

BEGIN

Insert Into @retAuthors

select au_lname, au_fname

from authors

order by au_lname, au_fname

RETURN

END

Regards,

Tom

Thomas Farren

Calvin Lawson SSChampion Points: 11030 More actions · Answer 7

-------------------------------------

Have to give Frank credit for this!

There was a URL to MS discussing it. Did caution on large tables.

Maybe Frank still has the URL.

Use PUBSGoselect rank=count(*), a1.au_lname, a1.au_fname from authors a1, authors a2 where a1.au_lname + a1.au_fname >= a2.au_lname + a2.au_fname group by a1.au_lname, a1.au_fname order by 1

Cheers,

Crispin

------------------------------------------

That's pretty cool. If you take the group by off you can see what it's doing...basically a cross join on every record in a1 with every record in a2 that's less than it.

This does the same thing, and costs less:

select

(Select count(*)

From Authors a1

Where

a1.au_lname >= a2.au_lname

and a1.au_fname >= a2.au_fname),

a2.au_lname,

a2.au_fname

from authors a2

order by 1

This method would scale for larger recordsets as long as the table is joined on it's PRIMARY KEY.

Signature is NULL

Frank Kalis SSC Guru Points: 111183 More actions · Answer 8

Although I use these methods myself, I support Jonathan in that you *should* consider doing this not on the server, but within your reporting engine.

I know this very easily be done with Access Report generator and I bet other programs have a similar functionality.

Just add a text box to your report and set the following properties for it.



Property      Setting

RunningSum    Over Group or Over All

ControlSource =1

Works like a charm.

Frank

http://www.insidesql.de

Edited by - Frank Kalis on 10/30/2003 01:09:15 AM

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Jonathan SSC-Insane Points: 20427 More actions · Answer 9

quote:
Although I use these methods myself, I support Jonathan in that you *should* consider doing this not on the server, but within your reporting engine.
I know this very easily be done with Access Report generator and I bet other programs have a similar functionality.
Just add a text box to your report and set the following properties for it.

Property Setting
RunningSum Over Group or Over All
ControlSource =1
Works like a charm.
Frank
http://www.insidesql.de

I don't know Acess, but it's that easy in Crystal, and also easy in Excel, which I do know pretty well:



  Range(Cells(1, 2), Cells(aRst.RecordCount, aRst.Fields.Count) + 1) = Application.Transpose(aRst.GetRows)

  Range(Cells(1, 1), Cells(aRst.RecordCount, 1)).FormulaR1C1 = "=RANK(RC5,R1C5:R" & aRst.RecordCount & "C5)"

--Jonathan

Frank Kalis SSC Guru Points: 111183 More actions · Answer 10

<*grin*>I know!

Excel is bread and butter to my daily work.

No need to do some VBA in Excel. Functionality is already there when you take a look at how you can modify your toolbar. Some very precious command waiting there to make Excel even more handy.

Frank

http://www.insidesql.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Jonathan SSC-Insane Points: 20427 More actions · Answer 11

quote:
<*grin*>I know!
Excel is bread and butter to my daily work.
No need to do some VBA in Excel. Functionality is already there when you take a look at how you can modify your toolbar. Some very precious command waiting there to make Excel even more handy.
Frank
http://www.insidesql.de

Well, sure. But I was just showing how to drop a "ranked" result set into Excel without ranking it at the server. By using an Excel funtion, you don't even need to order the result set by the value you use to rank. Plus, this being Excel, you can change the values in the spreadsheet and the rankings will automatically adjust.

--Jonathan

Frank Kalis SSC Guru Points: 111183 More actions · Answer 12

Indeed, Excel is a very nice tool. But on the other hand certainly has its limitations when it comes to 'real-time' DDE communication with Reuters or Bloomberg. Let's you get very familiar with some obscure guy called Dr. Watson.

Frank

http://www.insidesql.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]