How can i avoid nested query in SQL SERVER

Question

5.00/5 (1 vote)

See more:

This is my query. how can i avoid nested query.. please any one help me...

my table like this...event_team_player

player | score1 | event <br />
-----------------------<br />
7242   | 1.836  | 1<br />
2535   | 2.353  | 1<br />
7242   | 2.343  | 2<br />
7242   | 1.343  | 3

select * from
(select MIN(score1) as Your_score from event_team_player where player=7232 and score1>0) as t1, 
(select MIN(score1) as best,AVG(score1) as average,COUNT(distinct player) as no_of_player from event_team_player where score1>0)  as t2, 
(select rank from (select player,rank() over(order by (avg(score1))) as rank from event_team_player where score1>0 group by player) as a where a.player=7232) as t3

I got this result..

Your_score | best | average | no_of_player | rank<br />
--------------------------------------------------<br />
    1.24         | 0.90 | 2.2323  | 1735         | 12

Your score (min(score1)) is depent on perticlar player and best is (min(score1)),avg,no_of_player is depent on all player. Every player have one or more score1. Rank is perticlar player score1 avg..

Posted 30-Dec-11 0:07am

godbrother3

Updated 30-Dec-11 1:02am

v4

Add a Solution

Comments

Corporal Agarn 30-Dec-11 13:34pm

What version of SQL Server are you on?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

TRK3 · Accepted Answer · 2011-12-30T11:01:00

A couple of questions:

1. Do you actually need to keep every score that a player makes?

2. Do expect to query the datbase for high scores at least as often or way more often then you add scores to the database?

If you don't actually need to keep every score that a player makes, then I'd change what you store.

Use one table, that holds a single record for each player, in that record store:

player_id, best_score, total_score, number_of_scores

Separately, (in a separate table with only one record perhaps) store:

overall_best_score, overall_total_score, overall_number_of_scores

Doing it that way, you'd have to a little bit of extra work everytime that you store a new score, but retrieving the high scores and averages would be extremely quick, especially when you start getting lots of scores from players. Storing and retrieving it this way will use a lot less space and require a lot less computation.

If for some reason you do need to keep the individual scores, you could keep those in a separate table in addition to keeping the data I suggest you keep. That way the high score and average score retrieval will be very efficient and when you do need the actual individual scores you can reference the other table.

godbrother3 · Accepted Answer · 2012-01-01T20:01:00

<pre lang="SQL">select your_score,best,average,no_of_player,rank from
(select MIN(score1) as best,AVG(score1) as average,COUNT(distinct player) as no_of_player from event_team_player join event on event_team_player.event=event.id where score1>0 and event.official=1) as I
join
(select player,min(score1) as your_score,RANK() over(order by Avg(score1)) as rank from event_team_player join event on event_team_player.event=event.id where score1>0 and event.official=1 group by player) as J
on J.player=7232
</pre>

How can i avoid nested query in SQL SERVER

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0