CODEWITHSUNDEEP
asp.netsqldatabase
chetan Saini
chetan Saini

Reputation: 115

How to create database table dynamically and insert data selected by query

I'm working on website where I need to find rank of user on the basis of score. Earlier I'm calculating the score and rank of user by sql query .

select * from (
    select
        usrid,
        ROW_NUMBER()
        OVER(ORDER BY (count(*)+sum(sup)+sum(opp)+sum(visited)*0.3) DESC) AS rank,
        (count(*)+sum(sup)+sum(opp)+sum(visited)*0.3 ) As score
    from [DB_].[dbo].[dsas]
    group by usrid) as cash
where usrid=@userid

Please don't concentrate more on query because this is only to explain how I select data.

Problem: Now I can't use above query because every time I use rank it need to select rank from dsas table and data of dsas table is increasing day by day and slows down my website.

What I need is select data by above query and insert in another table named as score. Can we do anything like this?

Upvotes: 1

Views: 414

Answers (1)

dan1111
dan1111

Reputation: 6566

A better solution is to either include score as a field in your user table or have a separate table for scores. Any time you add new sup, opp, or visited data for a user, also recalculate their score at that time.

Then to get the highest ranking users, you will be able to perform a very simple select statement, ordering by score descending, and only fetching the number of rows you want. It will be very fast.

Upvotes: 2

Related Questions