Return the last 3 results based on total number of records in Table
Posted on 2007-10-11
I have a stored procedure, that is working 90% how I need it to work.
It is returning the last three results in the database based on a "ranking" based on the amounts they have won.
What I am trying to do is get their ranking to be the total amount of rows, minus their rank in reverse order. I.E
if there are 250 records in the table, the last person should have a ranking of 250, the person above, 249 and so one.
I have tried using update table, but the rank column is an inner view column (not sure if that is the right terminology) and doesnt actually exist on the table.
here is the query from within the stored proc.
set nocount on
select Alias, Yearmonth, Country, convert(int, round(PrizeAmount, 0)) as PrizeAmount into #tblMPVLast3Players from tblMPVLeaderBoard
where Yearmonth = @CurrentMonth
select top 3 * from
order by PrizeAmount asc
where Rank <
select count(*) as num from tblMPVLeaderBoard
where yearmonth = @CurrentMonth
--set Rank = (select sum(count(*)) from tblMPVLeaderBoard where yearmonth = @CurrentMonth)
--where Rank = 1
set nocount off
any ideas that i could try. maybe using join or by creating a view initally?
Thanks in advance