Row Number like in Yukon? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Row Number like in Yukon?

Author

Topic

shai
Starting Member

1 Post

Posted - 2005-07-05 : 20:41:31

Hi All,

I am after a way to implement a Row Number (like the one in Yukon), without using a temporary table (I don’t want to give users permission to create database objects).

Could someone point me in the right direct, the example is as follows…

For example data in below table...WaitingListTable

===================================================
CODE NAME DATEPLACED
===================================================
023 Mary 1 Jun 05
013 John 3 Apr 03
001 Peter 9 Dec 04
107 Jane 4 Oct 04
098 Liam 2 Jul 05
===================================================

Say you wanted the people on the waiting list ordered by the date, but also a rank to see where they lay within the waiting list (ie be able to say “there are X many people before you in the list at this time John”)

SELECT Code, Name, DatePlaced, [Some sort of Rank] Rank
FROM WaitingListTable
ORDER By DatePlaced ASC

Would give…

=============================================================
CODE NAME DATEPLACED RANK
=============================================================
013 John 3 Apr 03 1
107 Jane 4 Oct 04 2
001 Peter 9 Dec 04 3
023 Mary 1 Jun 05 4
098 Liam 2 Jul 05 5
=============================================================

How would it be possible to do such a thing?

Thanks heaps!
Shai

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-07-05 : 20:55:18

Use a stored procedure and either a table variable or temp table. Users don't need to have permissions to create "database objects" to create a temp table or table variable.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

andy8979
Starting Member

36 Posts

Posted - 2005-07-06 : 03:35:35

Yes good idea I was also looking for something like this but we can do one thing just create a table variable with a identity column and insert the data in the table and then again insert the data from this table variable to the main table It may sound a step more but its a good option
if some one has some other idea please let me know.

Thanks in Advance .

Anuj.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-07-06 : 08:46:58

Why add the overhead of calculating "row numbers"? For the situation you described, a simple select works fine:

select Count(*) as UsersAhead
from SomeTable
Where DatePlaced < @DatePlaced

or something like that.

More importantly, 9 times out of 10 you can also simply calculate or keept track of "row numbers" at the client or presentation layer, and not in the database, as you read in the data; i.e., in ASP or VB or ADO/ADO.NET this is quite easy to do with a simple integer variable as you loop through the rows returned. .NET DataTables have inherent "row numbers" as well. Crystal Reports and Access and most other reporting tools let you easily assign row numbers (sometimes hidden in the form of "running totals" in which you increment the constant 1).

- Jeff

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-07-06 : 09:44:51

what is this obsession with row numbers? Use Access if you REALLY need row numbers!

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-07-06 : 09:49:37

Access doesn't provide row numbers any more than SQL Server does ...

I think you mean they should use EXCEL!

- Jeff

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-07-06 : 10:15:22

whoops

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

Stellarion
Starting Member

5 Posts

Posted - 2005-07-07 : 13:39:09

This should get you the answer you are looking for.

SELECT TOP 100 PERCENT CODE, NAME, DATEPLACED,
(SELECT COUNT(*) FROM WaitingListTable b
WHERE b.DATEPLACED <= a.DATEPLACED ) AS RANK
FROM WaitingListTable a
ORDER BY RANK

The Future is fixed...The Past always changing.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources