asked on

Return the last 3 results based on total number of records in Table

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
            (
            select ROW_NUMBER()
            over
                  (
                  order by PrizeAmount asc
                  )
            as Rank,
            *
            from
            #tblMPVLast3Players
            )
      #tblMPVLast3Players
      where Rank <
(
                        select count(*) as num from tblMPVLeaderBoard
                        where yearmonth = @CurrentMonth
                        )

--update #tblMPVLast3Players
--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

Daniel Wilson

Can you provide a few rows of sample data? There's something here I'm not quite following.

byronrode

ASKER

@DanielWilson:

Thanks,

Here is some summy data, from the original table. Structure is identical, data is made up

Original Table: tblMPVLeaderboard

LeaderBoard ID | Alias | Yearmonth | Country | Prizeamount
--------------------------------------------------------------------------------------
213 | thebestplayer | 200710 | Argentina | 413.25
157 | iAmtheKing | 200710 | Finland | 230.75
213 | bob1234 | 200710 | USA | 75.00
12 | james1 | 200710 | UK | 12.00
987 | simonwashere | 200710 | USA | 1034.50
211 | gimmeyourlove | 200710 | UK | 406.25
201 | thebestman | 200710 | USA | 101.75

*total records are 7*

new Temp table: #tblMPVLast3Players (from current Stored Proc)

Rank | Alias | Yearmonth | Prizeamount
------------------------------------------------------------
1 | james1 | 200710 | 12
2 | bob1234 | 200710 | 75
3 | thebestman | 200710 | 102

What I would like to have returned based on the above but add the record number to the ranking (or subtract in logic) and reverse the order.

temp table #tblMPVLast3Players

Rank | Alias | Yearmonth | Prizeamount
------------------------------------------------------------
5 | thebestman | 200710 | 102
6 | bob1234 | 200710 | 75
7 | james1 | 200710 | 12

Does this make sense?

Daniel Wilson

What about:
==========================================
set nocount on

select Alias, Yearmonth, Country, convert(int, round(PrizeAmount, 0)) as PrizeAmount into #tblMPVLast3Players from tblMPVLeaderBoard
where Yearmonth = @CurrentMonth

select top 3 totalCount-Rank as NewRank, * from
(
select ROW_NUMBER()
over
(
order by PrizeAmount asc
)
as Rank,
count(*) as totalCount,
*
from
#tblMPVLast3Players
)
#tblMPVLast3Players
where Rank <
(
select count(*) as num from tblMPVLeaderBoard
where yearmonth = @CurrentMonth
)

--update #tblMPVLast3Players
--set Rank = (select sum(count(*)) from tblMPVLeaderBoard where yearmonth = @CurrentMonth)
--where Rank = 1

set nocount off

============================================

byronrode

ASKER

DanielWilson:

Definitely on the right track, but the structure was incorrect. When I ran your suggestion I got the famous "GROUP BY" error, so I attempted adding a GROUP BY clause, which stopped the error but caused the "count(*) as totalCount" to count each row, so totalCount became 1 throughout and then gave me negative numbers for the NewRank column.

What I did was change your addition slightly and wrap it in parenthesis and it solved the problem and removed the need for the GROUP BY clause:

Old Code
=======

as Rank,
count(*) as totalCount,
*

New Code
========

as Rank,
      (
            select count(*)
            from tblMPVLeaderBoard
            where Yearmonth = @CurrentMonth
      )
      as totalCount,
*

Thanks for the suggestion, I could kick myself now for not thinking that.
Now just to get it to go in reverse order, as an ORDER BY clause reverses the initial data and not the results.

ASKER CERTIFIED SOLUTION

Daniel Wilson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

byronrode

ASKER

I was thinking of wrapping it in another select statement.
That did the trick. Thanks a ton :)