• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 687
  • Last Modified:

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

ok...
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)


Ashish
0
ashishg55
Asked:
ashishg55
  • 5
  • 3
  • 3
  • +3
2 Solutions
 
fefo_33065Commented:
you can try:

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

and see
0
 
kamichieCommented:
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.
0
 
ashishg55Author Commented:
ahem...
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


Ashish
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
sasapopovicCommented:
Hi,

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.

Regards,
Sasa
0
 
Jim P.Commented:
Select AthleteID, Count(*) As NumGames
From MyTable
Group By AthleteID
Oder By NumGames desc
0
 
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.

Ashish
0
 
sasapopovicCommented:
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
      for
            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
                  end
            end
            set @prevPoints = @currPoints

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

      if(@allOrdered = 1) begin
            print 'All Points are ordered fine'
      end else begin
            print 'Points are not ordered fine'
      end
0
 
sasapopovicCommented:
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.
0
 
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.
0
 
Jim P.Commented:
And without the order by the database returns in whatever order it wants to.
0
 
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.....????!!!????!!!!!


Ashish
0
 
ashishg55Author Commented:
and you dont have to think in order way if thats not even possible with what i am doing.
0
 
nagkiCommented:
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.
0
 
ashishg55Author Commented:
thanks guys
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 5
  • 3
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now