create an incremental counter in the stored procedure

Hello, I have a following SP
I want to add an extra field "ranking" that just increments the row number.
Another feature would be: if several users have an equal totalvalue, they
should have an equal ranking number. the rankings following users would have
to be adjusted as well. thanks

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE [dbo].[Rankings]
@.iErrorCode int OUTPUT
AS

SELECT top 30
###COUNTER##,
[user],
[totalvalue], [cash], [stocksvalue]

FROM [dbo].[users]
ORDER BY totalvalue DESC

SELECT @.iErrorCode=@.@.ERROR

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOThere is more than one way to rank a set with tied values.

CREATE TABLE Users (userid INTEGER PRIMARY KEY, totalvalue NUMERIC(10,2) NOT
NULL, cash NUMERIC(10,2) NOT NULL, stocksvalue NUMERIC(10,2) NOT NULL)

INSERT INTO Users VALUES (101,1010,100,99)
INSERT INTO Users VALUES (102,2020,100,99)
INSERT INTO Users VALUES (103,3030,100,99)
INSERT INTO Users VALUES (104,3030,100,99)
INSERT INTO Users VALUES (105,1002,100,99)
INSERT INTO Users VALUES (106,1002,100,99)
INSERT INTO Users VALUES (107,1002,100,99)
INSERT INTO Users VALUES (108,1002,100,99)
INSERT INTO Users VALUES (109,1000,100,99)

See if this gives the result you expect:

SELECT COUNT(U2.totalvalue)+1 AS ranking,
U1.userid, U1.totalvalue, U1.cash, U1.stocksvalue
FROM Users AS U1
LEFT JOIN Users AS U2
ON U1.totalvalue < U2.totalvalue
GROUP BY U1.userid, U1.totalvalue, U1.cash, U1.stocksvalue
ORDER BY ranking, U1.userid

Result:

ranking userid totalvalue cash stocksvalue
---- ---- ---- ---- ----
1 103 3030.00 100.00 99.00
1 104 3030.00 100.00 99.00
3 102 2020.00 100.00 99.00
4 101 1010.00 100.00 99.00
5 105 1002.00 100.00 99.00
5 106 1002.00 100.00 99.00
5 107 1002.00 100.00 99.00
5 108 1002.00 100.00 99.00
9 109 1000.00 100.00 99.00

(9 row(s) affected)

Or maybe this:

SELECT COUNT(DISTINCT U2.totalvalue) AS ranking,
U1.userid, U1.totalvalue, U1.cash, U1.stocksvalue
FROM Users AS U1
LEFT JOIN Users AS U2
ON U1.totalvalue <= U2.totalvalue
GROUP BY U1.userid, U1.totalvalue, U1.cash, U1.stocksvalue
ORDER BY ranking, U1.userid

Result:

ranking userid totalvalue cash stocksvalue
---- ---- ---- ---- ----
1 103 3030.00 100.00 99.00
1 104 3030.00 100.00 99.00
2 102 2020.00 100.00 99.00
3 101 1010.00 100.00 99.00
4 105 1002.00 100.00 99.00
4 106 1002.00 100.00 99.00
4 107 1002.00 100.00 99.00
4 108 1002.00 100.00 99.00
5 109 1000.00 100.00 99.00

(9 row(s) affected)

--
David Portas
SQL Server MVP
--|||thank you! it works fine.

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> schrieb im
Newsbeitrag news:1pmdnW4hl-GFFt3dRVn-uA@.giganews.com...
> There is more than one way to rank a set with tied values.
> CREATE TABLE Users (userid INTEGER PRIMARY KEY, totalvalue NUMERIC(10,2)
NOT
> NULL, cash NUMERIC(10,2) NOT NULL, stocksvalue NUMERIC(10,2) NOT NULL)
> INSERT INTO Users VALUES (101,1010,100,99)
> INSERT INTO Users VALUES (102,2020,100,99)
> INSERT INTO Users VALUES (103,3030,100,99)
> INSERT INTO Users VALUES (104,3030,100,99)
> INSERT INTO Users VALUES (105,1002,100,99)
> INSERT INTO Users VALUES (106,1002,100,99)
> INSERT INTO Users VALUES (107,1002,100,99)
> INSERT INTO Users VALUES (108,1002,100,99)
> INSERT INTO Users VALUES (109,1000,100,99)
> See if this gives the result you expect:
> SELECT COUNT(U2.totalvalue)+1 AS ranking,
> U1.userid, U1.totalvalue, U1.cash, U1.stocksvalue
> FROM Users AS U1
> LEFT JOIN Users AS U2
> ON U1.totalvalue < U2.totalvalue
> GROUP BY U1.userid, U1.totalvalue, U1.cash, U1.stocksvalue
> ORDER BY ranking, U1.userid
> Result:
> ranking userid totalvalue cash stocksvalue
> ---- ---- ---- ---- ----
> 1 103 3030.00 100.00 99.00
> 1 104 3030.00 100.00 99.00
> 3 102 2020.00 100.00 99.00
> 4 101 1010.00 100.00 99.00
> 5 105 1002.00 100.00 99.00
> 5 106 1002.00 100.00 99.00
> 5 107 1002.00 100.00 99.00
> 5 108 1002.00 100.00 99.00
> 9 109 1000.00 100.00 99.00
> (9 row(s) affected)
> Or maybe this:
> SELECT COUNT(DISTINCT U2.totalvalue) AS ranking,
> U1.userid, U1.totalvalue, U1.cash, U1.stocksvalue
> FROM Users AS U1
> LEFT JOIN Users AS U2
> ON U1.totalvalue <= U2.totalvalue
> GROUP BY U1.userid, U1.totalvalue, U1.cash, U1.stocksvalue
> ORDER BY ranking, U1.userid
> Result:
> ranking userid totalvalue cash stocksvalue
> ---- ---- ---- ---- ----
> 1 103 3030.00 100.00 99.00
> 1 104 3030.00 100.00 99.00
> 2 102 2020.00 100.00 99.00
> 3 101 1010.00 100.00 99.00
> 4 105 1002.00 100.00 99.00
> 4 106 1002.00 100.00 99.00
> 4 107 1002.00 100.00 99.00
> 4 108 1002.00 100.00 99.00
> 5 109 1000.00 100.00 99.00
> (9 row(s) affected)
> --
> David Portas
> SQL Server MVP
> --

CPU Usage vs Catalog Population

I have an SQL account that uses FTS on SQL 2000.
I originally used Incremental Population to index the work done during the
day.
However, the indexing process was carrying over into the next work day.
Therefore, last week I changed the method to Change Tracking with Update
Index in Background (with John Kane's urging) to try to alleviate the loss
of performance being experienced during the day by the system's users.
It is my understanding that CT w/UIB would only perform indexing when the
CPU is idle and resources are available. By switching to this method, I
thought the overall performance would improve.
This morning I checked the SQL Server, CPU usage is averaging 60% and the
catalog status reports Population in Progress.
Consequently, all users are unable to perform logins, searches, ... because
of timeouts against the database.
I am remote to the application and finally got the menu up to issue the
command to stop population.
Q1:
I thought the Change Tracking with Update Index in Background option is to
prevent high CPU utilization.
Should I be seeing such high utilization with this option selected to the
point that other applications are timing out when trying to make a
connection to the database? Enterprise manager also times out when trying to
connect.
Q2:
The stop command has finally executed and the catalog status now reports
idle.
It has been over an hour and the CPU usage is still very high (50 - 60%).
Is this continued usage the result of the indexing process?
Is there residual processing that could go on for hours after the status
reports idle?
Q3:
Related to Q1, since the population status reports idle but I still see high
CPU utilization, how do I isolate what in particular is keeping the usage so
high? How to prove it is mssearch and not something else going on with the
database?
I see the sqlserver and mssearch processes listed in task mgr with high cpu
& memory usage.
Update index in background does not necessarily index when the CPU is idle
or resources are available. Rather it polls a table every second and indexed
rows which are marked to be re-indexed.
You might want to try a scheduled Update Index at intervals that work for
your requirements. For instance update the index every 1/2 hour.
If your table you are indexing is heavily updated you may run into these
problems with relatively high CPU utilization, you will also run into these
problems if your content is in German or Far East languages.
High CPU utilization just means your processor is busy, it isn't by itself a
bad thing. When it starts to cause execessive paging or create queueing is
when it can degrade overall system performance.
To isolate particular processes using high cpu you can use Performance
Monitor or simply sort the processes you find in Task Manager by CPU. You
can also run sp_who2 to get an idea of cumulative CPU and disk IO.
"RGondzur" <rgondzur@.NO_SPAM_aicsoft.com> wrote in message
news:es8K7UqFEHA.3336@.TK2MSFTNGP12.phx.gbl...
> I have an SQL account that uses FTS on SQL 2000.
> I originally used Incremental Population to index the work done during the
> day.
> However, the indexing process was carrying over into the next work day.
> Therefore, last week I changed the method to Change Tracking with Update
> Index in Background (with John Kane's urging) to try to alleviate the loss
> of performance being experienced during the day by the system's users.
> It is my understanding that CT w/UIB would only perform indexing when the
> CPU is idle and resources are available. By switching to this method, I
> thought the overall performance would improve.
> This morning I checked the SQL Server, CPU usage is averaging 60% and the
> catalog status reports Population in Progress.
> Consequently, all users are unable to perform logins, searches, ...
because
> of timeouts against the database.
> I am remote to the application and finally got the menu up to issue the
> command to stop population.
>
> Q1:
> I thought the Change Tracking with Update Index in Background option is to
> prevent high CPU utilization.
> Should I be seeing such high utilization with this option selected to the
> point that other applications are timing out when trying to make a
> connection to the database? Enterprise manager also times out when trying
to
> connect.
>
> Q2:
> The stop command has finally executed and the catalog status now reports
> idle.
> It has been over an hour and the CPU usage is still very high (50 - 60%).
> Is this continued usage the result of the indexing process?
> Is there residual processing that could go on for hours after the status
> reports idle?
>
> Q3:
> Related to Q1, since the population status reports idle but I still see
high
> CPU utilization, how do I isolate what in particular is keeping the usage
so
> high? How to prove it is mssearch and not something else going on with the
> database?
> I see the sqlserver and mssearch processes listed in task mgr with high
cpu
> & memory usage.
>
>