[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 227
  • Last Modified:

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
0
byronrode
Asked:
byronrode
  • 3
  • 3
1 Solution
 
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
 
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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

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


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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now