Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

RowNum Function in SQLServer Expand / Collapse
Author
Message
Posted Tuesday, October 28, 2003 1:20 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 2:48 AM
Points: 912, Visits: 210
Is there any function in SQL Server like RowNum in Oracle








My Blog: http://dineshasanka.spaces.live.com/
Post #17662
Posted Tuesday, October 28, 2003 1:28 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, July 14, 2014 3:32 PM
Points: 917, Visits: 412
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!
Post #84937
Posted Tuesday, October 28, 2003 1:31 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, December 9, 2014 5:36 AM
Points: 5,956, Visits: 286
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/
Post #84938
Posted Tuesday, October 28, 2003 1:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, December 9, 2014 5:36 AM
Points: 5,956, Visits: 286
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/
Post #84939
Posted Tuesday, October 28, 2003 1:33 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 2:48 AM
Points: 912, Visits: 210
Thank You Both guys








My Blog: http://dineshasanka.spaces.live.com/
Post #84940
Posted Tuesday, October 28, 2003 7:18 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, September 23, 2009 9:40 AM
Points: 940, 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
Post #84941
Posted Wednesday, October 29, 2003 11:26 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 30, 2005 11:12 AM
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
Post #84942
Posted Wednesday, October 29, 2003 1:03 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, June 21, 2011 10:03 AM
Points: 577, 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
Post #84943
Posted Thursday, October 30, 2003 1:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, December 9, 2014 5:36 AM
Points: 5,956, Visits: 286
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/
Post #84944
Posted Thursday, October 30, 2003 5:57 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, September 23, 2009 9:40 AM
Points: 940, 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
Post #84945
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse