Sql querry help...orderby/except all???

here is what i am trying to do.
i have a table with 2 fields. number of games the atheletes played and their points.
i want to state if the following hypothesis is right or wrong.
the more games the athletes plays the more points he or she will get.
so basically i want to make a query where i order the table by the number of athlete and see if the points are also ordered.
this query is giving me a headache....please help.... 500 points for easy question (for someone who knows this stuff)

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

you can try:

select numberofgames, points from table order by numberofgames, points desc

and see
Assuming you table is name Data, and the fields are called NumAthletes and NumPoints this is the SQL query you need.

Select * From Data OrderBy NumAthletes

If that doesnt work let me know what database you are using (eg Access, MySQL, SQL Server, Oracle, and etc.) and also how you are pulling the Query ADO, RDO, .Net Data Objects, Built in Query Builder.
ashishg55Author Commented:
i dont want to order the number of points.
i want to check that they are in order when i order the number of games.
that way i can state my hypothesis that more number of games mean more points.

for example

NumGames    NumPoints
10                      15
8                        10
6                         5
5                          1

that means when i ordered numgames numpoijnts also got ordered hence my query is true.

if this happens instead
NumGames    NumPoints
10                      15
8                        5
6                         10
5                          1

that means that NumPoints were not in order and hence hypothesis is false.

Hope you understand what i mean by that. and for the database i am using DB2 on Unix

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!


I'm not sure how exactly your database looks like but here are some examples you can test with:

      declare @game table (AthleteID int, NumberOfGames int, Points int)
      insert into @game values(1, 10, 20)
      insert into @game values(2, 5, 15)
      insert into @game values(2, 7, 13)
      insert into @game values(1, 2, 1)
      insert into @game values(3, 3, 4)
      insert into @game values(3, 1, 0)
      insert into @game values(1, 2, 2)
      select * from @game
      order by NumberOfGames desc, Points desc

The @game table have AthleteID beside two columns you mentioned. If you execute above query, you will see next result:

      1      10      20
      2      7      13
      2      5      15
      3      3      4
      1      2      2
      1      2      1
      3      1      0
That means that all rows form table are ordered by by number of games and if there are games with the same number of athletes then those rows are ordered by number of points.

You maybe need something like this:

      declare @game table (AthleteID int, NumberOfGames int, Points int)
      insert into @game values(1, 10, 20)
      insert into @game values(2, 5, 15)
      insert into @game values(2, 7, 13)
      insert into @game values(1, 2, 1)
      insert into @game values(3, 3, 4)
      insert into @game values(3, 1, 0)
      insert into @game values(1, 2, 2)
      select AthleteID, sum(NumberOfGames) as [TotalGames], sum(Points) as [TotalPoints] from @game
      group by AthleteID
      order by 2 desc, 3 desc
The result of that query would be:

      1      14      23
      2      12      28
      3      4      4
Which means that points for each athlete are sumarized and number of games for each athelete are also sumarized. Rows are ordered by the sum of all games for each athelete and if there are more athletes with the same sum of all played games then rows are ordered by the sum of all points for particular athelete.

I hope this will help you. If this is not what you need, please post some more info.

Jim P.Commented:
Select AthleteID, Count(*) As NumGames
From MyTable
Group By AthleteID
Oder By NumGames desc
ashishg55Author Commented:
my point of this question is to get the answer for the hypothesis. not really order stuff. that was something i thought would help you undertand what i am doing.
i just want true or false as the answer...

hope that piece of info helps.

Here it is:
      declare @game table (NumberOfGames int, Points int)
      insert into @game values(10, 20)
      insert into @game values(5, 15)
      insert into @game values(7, 13)
      insert into @game values(2, 1)
      insert into @game values(3, 4)
      insert into @game values(1, 0)
      insert into @game values(2, 2)
      declare @currNumberOfGames int, @currPoints int
      declare @prevPoints int
      declare @allOrdered bit
      set @allOrdered = 1
      declare athletesCursor cursor
            select NumberOfGames, Points
            from @game
            order by NumberOfGames desc, Points desc

      open athletesCursor
      fetch next from athletesCursor
            into @currNumberOfGames, @currPoints
      while @@fetch_status = 0 begin
            if(@prevPoints is not null) begin
                  if(@prevPoints < @currPoints) begin
                        set @allOrdered = 0
            set @prevPoints = @currPoints

            fetch next from athletesCursor
                  into @currNumberOfGames, @currPoints
      close athletesCursor
      deallocate athletesCursor

      if(@allOrdered = 1) begin
            print 'All Points are ordered fine'
      end else begin
            print 'Points are not ordered fine'
I just forgot to ask if that is in MS SQL Server or some other RDBMS. I wrote SQL script that works fine in SQL Server. If you need it in some other language, it should not be a problem to change it.

I hope that is what you need.
Jim P.Commented:
The catch is that if they play, but don't get any points, or very little then they could show up farther down the list than a player that has played only five but accumulated more points.
Jim P.Commented:
And without the order by the database returns in whatever order it wants to.
ashishg55Author Commented:
well i cant have sqc programs. i need a simple sql querry to do what i am doing :(
just need to check if i order games column the points also was ordered with it. if yes then return true.

like i could do this by creating two different tables.  then ordering one by games. one by points and subtract them to see if it has anything in it or not. would that work.....????!!!????!!!!!

ashishg55Author Commented:
and you dont have to think in order way if thats not even possible with what i am doing.
Hope this query will help you:

select count(*) from Table t where t.NumPoints < (select max(NumPoints) from Table where NumGames < t.NumGames)

For each player the above query will calculate the max(NumPoints) of all players who played less no of games than the current one and depending on output we will have two cases.

1)if NumPoints of current one is less than max(NumPoints) : indicates that a player who played less no of games than the current one had more points.
2)if NumPoints of current one is greater than max(NumPoints): indicates no player who played less no of games that the current one had more points.

If the above count(*) returns 0 that means that your hypothesis is true.
If it returned more than 0 then your hypothesis is false.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ashishg55Author Commented:
thanks guys
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.