Quick ORDER by question

chinto

Hey Folks,

I am using an ORDER by clause to list out entries in DESC order according to and AVG(). My problem is some entries do not have an AVG() and are being listed first, after that it is all working the way i want. My question is how do I get the entries without a score at all to be listed on the bottom.

This is what i am looking to get:

Person----------Average
Jim---------------220
Joe--------------198
Bill---------------196
Sam-------------95
Jack--------------no average
Bob--------------no average

This is what I am currently getting:
Bob---------------no average
Jack--------------no average
Jim---------------220
Joe--------------198
Bill---------------196
Sam-------------95

What I need to do is move Bob and Jack down to the end of the list as they have no scores recorded yet. But I need to keep them on the list.

I am thinking there is a simple solution to this, Im just missing it somehow.

Thanks in Advance,

bastien

set the default entry to '0' for avg...therefore the user will always have an avg of 0 and be at the bottom of the list...

chinto

The problem with setting the default value to 0 is when scores are added it skews the numbers. For example:

Table Scores:

Name ----- Score
Bill ----------0
Bill ---------100
Bill ---------200

Now Bill's true average is 150 as he has only 2 scores that add up to 300. But because the initial value is 0 it adds into the total and Bill's average is skewed down to 100.

bastien

consider then separating the user name data from the table that holds the scores data...

update the scores data whenever the user actually adds a score...then the averages won't skew...this wil solve the problem

then just add another query to select those user who have not added scores yet to diplay their names with some other message...

Lars_Berg

select name,coalesce(avg(score),-1) as score, from t
group by name
order by score desc

Then you can map the -1 to no score in the application.