CODEWITHSUNDEEP
sql-serverpagination
Tjodalv
Tjodalv

Reputation: 370

SQL Server paginate complex query

I want to paginate hiscore table. I have 2 tables:

gs_score_table

id (auto increment int)
project_id (int)
game_id (int)
user_id (int)
entry_date (datetime)
score (int)

users

id (auto increment int)
user_name (varchar)

What I want is to get list of hiscores and order list by scores DESC, but I always get error in line 5 (this: ROW_NUMBER() OVER (ORDER BY total_score DESC) AS RowNumber), saying:

Invalid column name 'total_score'.

Can anyone help please.

SELECT TOP 50
    *
FROM
    (SELECT
        ROW_NUMBER() OVER (ORDER BY total_score DESC) AS RowNumber,
        gs.user_id,
        users.user_name,
        SUM(gs.score) AS total_score, 
        (SELECT COUNT(gs2.id) FROM gs_score_table AS gs2 WHERE gs2.user_id = gs.user_id AND gs2.game_id = 1) AS games_played,
        TotalRows=Count(*) OVER()
    FROM
        gs_score_table AS gs
    INNER JOIN
        users ON users.id = gs.user_id
    WHERE
        gs.project_id = 2
        AND gs.game_id = 1
        AND CAST(gs.entry_date AS date) BETWEEN '2012-04-23' AND '2012-04-23'
    GROUP BY
        gs.user_id, users.user_name) _tmpInlineView WHERE RowNumber >= 1

Upvotes: 0

Views: 206

Answers (1)

AdaTheDev
AdaTheDev

Reputation: 147224

You can't use the alias "total_score" in the ROW_NUMBER ORDER BY clause. Instead, you need:

ROW_NUMBER() OVER (ORDER BY SUM(gs.score) DESC) AS RowNumber

Upvotes: 1

Related Questions