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
LVL 1
byronrodeAsked:
Who is Participating?
 
Daniel WilsonConnect With a Mentor Commented:


set nocount on

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

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

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

set nocount off
0
 
Daniel WilsonCommented:
Can you provide a few rows of sample data?  There's something here I'm not quite following.
0
 
byronrodeAuthor Commented:
@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?
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
Daniel WilsonCommented:
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

============================================
0
 
byronrodeAuthor Commented:
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.
0
 
byronrodeAuthor Commented:
I was thinking of wrapping it in another select statement.
That did the trick. Thanks a ton :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.