Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


RowNum Function in SQLServer


RowNum Function in SQLServer

Author
Message
Dinesh Asanka
Dinesh Asanka
SSC Eights!
SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)

Group: General Forum Members
Points: 914 Visits: 223
Is there any function in SQL Server like RowNum in Oracle






My Blog: http://dineshasanka.spaces.live.com/
Crispin Proctor
Crispin Proctor
SSC Eights!
SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)

Group: General Forum Members
Points: 917 Visits: 414
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,
Crispin


I 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
Frank Kalis
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 5971 Visits: 289
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/
Frank Kalis
Frank Kalis
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 5971 Visits: 289
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/
Dinesh Asanka
Dinesh Asanka
SSC Eights!
SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)

Group: General Forum Members
Points: 914 Visits: 223
Thank You Both guys






My Blog: http://dineshasanka.spaces.live.com/
Jonathan
Jonathan
SSC Eights!
SSC Eights! (941 reputation)SSC Eights! (941 reputation)SSC Eights! (941 reputation)SSC Eights! (941 reputation)SSC Eights! (941 reputation)SSC Eights! (941 reputation)SSC Eights! (941 reputation)SSC Eights! (941 reputation)

Group: General Forum Members
Points: 941 Visits: 54
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



--Jonathan
tfarren
tfarren
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 1
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


Thomas Farren
Calvin Lawson
Calvin Lawson
Mr or Mrs. 500
Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)

Group: General Forum Members
Points: 582 Visits: 102
-------------------------------------

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
Frank Kalis
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 5971 Visits: 289
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/
Jonathan
Jonathan
SSC Eights!
SSC Eights! (941 reputation)SSC Eights! (941 reputation)SSC Eights! (941 reputation)SSC Eights! (941 reputation)SSC Eights! (941 reputation)SSC Eights! (941 reputation)SSC Eights! (941 reputation)SSC Eights! (941 reputation)

Group: General Forum Members
Points: 941 Visits: 54
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



--Jonathan
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search