Ranking 1

[pwbrown]

In the query, there is a calculated field that has a number either a positive or negative number, possibly 0. What I need the query to do is arrange the data from the lowest number to the highest like
-66
-45
-30
0
34
45
61
61
(Yes there maybe duplicates, but within the duplicate, it does not matter which one comes first)

Now according to this I need a new field to rank like this
-66 1
-45 2
-30 3
0 4
34 5
45 6
61 7
61 8

If I can get this to work, the next thing is to have a field "Rank" in the table be updated by the rank assigned by the query.

I don't even know if this is possible but sure would make everything run easier and take out user error in assigning ranking.

 

[Remou]

A search for keyword ranking returns a great many posts. Perhaps one may suit.

 

[pwbrown]

I have tried looking at the key word, and looks like one would help, but I am getting lost in what to put where....

 

[Remou]

The best bet is to post the SQL you have found and any modifcations you have made to make it work for you.

 

[pwbrown]

Below is the current SQL that I have been working on. However it only shows the total and every record has the ranking of 12. So there are 12, 12's

SELECT (SELECT Count (*) From ranking where total <= ranking.total) AS ranking, s.Total
FROM ranking AS s
ORDER BY 1;

"Ranking" = query that has a caculated field "Total" that is the sum of all the days.

This SQL is in the query "Rank"

However, this also needs to be modified so that it will update a table "Player Infomation" field "Rank". Having it as an Select query really does not do any good. But I thought it was a start.

Thanks again for all the help.

 

[cmmrfrds]

Assuming the table has an unique ID number, a self join will create a rank.

SELECT B.id, max(A.mynumbers) AS num, Count(*) AS Rank
FROM SomeNumbers AS A, somenumbers AS B
WHERE (((A.ID)<=B.id))
GROUP BY B.id;

 

[pwbrown]

What is meant by "unique ID number"? Also, am lost as to what "somenumbers as a"... I am an extreme novice at SQL, any guidance is greatly appreicated.

 

[Golom]

somenumbers As A is the assignment of a table alias (A) to the table "Somenumbers". You will then refer to this instance of table "SomeNumbers" by its alias. There can be more than one instance of the same table in a query.

In the SQL you provided you need

SELECT (SELECT Count (*) From ranking where total <= [COLOR=red]S[/color].total) AS ranking, s.Total
FROM ranking AS s
ORDER BY 1;

The query is called a "Correlated subquery" and it means that your main query has another query inside it and that "inside" query depends on a value from the outer query. The way you had it before, it was not correlated (i.e. there was no reference to the outer query in the inner one.) When that happens, the inner query is evaluated only once and the same value is used for all records (12 in your case.)

cmmrfrds has provided an alternative but equivalent way of doing it using a join of two instances of the same table instead of a correlated sub-query. In his query there is a field called B.id and if the values in that field are not duplicated (i.e. they are unique) then you will get rankings that are different for each record. If they are not unique (i.e. two or more records have the same ID) then those records will all have the same computed ranking.

 

[pwbrown]

It is somewhat working now. The problem is that the score goes from 57 for ranking 10 to (2) 61's. In the query each 61 is ranked as 12. I need one to be ranked 11 and the second to be ranked 12. When the score is the same, I don't care how it differenciates which one would be 11 and the other 12, but I can not go from 10 to (2) 12's. I can add another field if necessary, but I am not sure.
I guess that shows I need to use cmmrfrds way of doing things, but not sure if I need to start a new query, or add code to an existing query.

The main query "Scores" has all the fields from the table "Player Information" including the primary key which is an autonumber that numbered each player "Player #".

Scores simply computes the over/under for each day and totals the over / under in the expression "Total". This expression "Total" is what I need to base another expression "Ranking". I believe if I can get the ranking in this main query, I can avoid an update or admend query.

Here is the current SQL from "Scores"
CODE
SELECT [Player Information].[Player #], [Player Information].Player, [Player Information].Handicap, [Player Information].Sunday, [Player Information].Monday, IIf([Monday] Is Null,0,([Monday]-[Handicap])-72) AS [Mon +/-], [Player Information].Tuesday, IIf([Tuesday] Is Null,0,([Tuesday]-[Handicap])-72) AS [Tues +/-], [Player Information].Wednesday, IIf([Wednesday] Is Null,0,([Wednesday]-[Handicap])-72) AS [Wed +/-], [Player Information].Thursday, IIf([Thursday] Is Null,0,([Thursday]-[Handicap])-72) AS [Thurs +/-], [Player Information].Friday, IIf([Friday] Is Null,0,([Friday]-[Handicap])-72) AS [Fri +/-], [Player Information].[Sunday $], [Player Information].[Monday $], [Player Information].[Tuesday $], [Player Information].[Wednesday $], [Player Information].[Thursday $], [Player Information].[Friday $], [Mon +/-]+[Tues +/-]+[Wed +/-]+[Thurs +/-]+[Fri +/-] AS Total
FROM [Player Information]
GROUP BY [Player Information].[Player #], [Player Information].Player, [Player Information].Handicap, [Player Information].Sunday, [Player Information].Monday, [Player Information].Tuesday, [Player Information].Wednesday, [Player Information].Thursday, [Player Information].Friday, [Player Information].[Sunday $], [Player Information].[Monday $], [Player Information].[Tuesday $], [Player Information].[Wednesday $], [Player Information].[Thursday $], [Player Information].[Friday $];

 

[Golom]

That's a bit tougher. You need a field or combination of fields that will be unique in order to generate unique ranks. Since I don't know what your specific data values are, I'm not sure what would be appropriate in your case but here's a simple example that you can use as a guide.
[tt]
myTable
pn total
1 5
2 7
3 4
5 7
6 9
8 11
9 6
10 5
[/tt]
Then the SQL

SELECT * 

FROM (

SELECT A.pn, A.total,  (Select Count(*) From myTable as x
WHERE (10000*x.total+X.pn) <= (10000*A.total + A.pn) ) As rank

FROM myTable as A

)  As XX

ORDER BY  3 ASC

Produces
[tt]
Result
pn total rank
3 4 1
1 5 2
10 5 3
9 6 4
2 7 5
5 7 6
6 9 7
8 11 8
[/tt]

The magic (if such there be) is that we have computed a unique value as a combination of two fields. Specifically,

10000 * Total + pn

where the + pn part just ensures different values for those cases where the value of "Total" is duplicated. "10000" is used quite arbitrarily to ensure that adding "pn" doesn't cause the computed value to change positions in the list of "total" values.

BTW: You don't need the GROUP BY clause because you are not using any aggregate functions in your query.

 

[pwbrown]

Do I need to start a new query with this SQL, or can I add it to the existing?
I tried adding this to the bottom which changed the code to the following, but syntax erros poped up all over the place...

SELECT [Player Information].[Player #], [Player Information].Player, [Player Information].Handicap, [Player Information].Sunday, [Player Information].Monday, IIf([Monday] Is Null,0,([Monday]-[Handicap])-72) AS [Mon +/-], [Player Information].Tuesday, IIf([Tuesday] Is Null,0,([Tuesday]-[Handicap])-72) AS [Tues +/-], [Player Information].Wednesday, IIf([Wednesday] Is Null,0,([Wednesday]-[Handicap])-72) AS [Wed +/-], [Player Information].Thursday, IIf([Thursday] Is Null,0,([Thursday]-[Handicap])-72) AS [Thurs +/-], [Player Information].Friday, IIf([Friday] Is Null,0,([Friday]-[Handicap])-72) AS [Fri +/-], [Player Information].[Sunday $], [Player Information].[Monday $], [Player Information].[Tuesday $], [Player Information].[Wednesday $], [Player Information].[Thursday $], [Player Information].[Friday $], [Mon +/-]+[Tues +/-]+[Wed +/-]+[Thurs +/-]+[Fri +/-] AS Total
FROM (

SELECT A.[player #], A.total,  (Select Count(*) From [Player Information] as x
WHERE (10000*x.total+X.[player #]) <= (10000*A.total + A.[player #]) ) As rank

FROM [Player Information] as A

)  As XX

ORDER BY  3 ASC

At least I finally figured out how to post the code correctly, but this is still driving me nuts. Thank you for your continuing help!

 

[Golom]

Lets call this

SELECT [Player #], Player, Handicap, Sunday, Monday, IIf([Monday] Is Null,0,([Monday]-[Handicap])-72) AS [Mon +/-], Tuesday, IIf([Tuesday] Is Null,0,([Tuesday]-[Handicap])-72) AS [Tues +/-], Wednesday, IIf([Wednesday] Is Null,0,([Wednesday]-[Handicap])-72) AS [Wed +/-], Thursday, IIf([Thursday] Is Null,0,([Thursday]-[Handicap])-72) AS [Thurs +/-], Friday, IIf([Friday] Is Null,0,([Friday]-[Handicap])-72) AS [Fri +/-], [Sunday $], [Monday $], [Tuesday $], [Wednesday $], [Thursday $], [Friday $], [Mon +/-]+[Tues +/-]+[Wed +/-]+[Thurs +/-]+[Fri +/-] AS Total
From [Player Information]

qryP

then

Select *
From
(
SELECT A.[player #], A.total, (Select Count(*) From qryP as x
WHERE (10000*x.total+X.[player #]) <= (10000*A.total + A.[player #]) ) As rank

FROM qryP as A

) As XX
Order by 3

It starts to look a bit messy if you do it all in-line

Select *
From
(
SELECT A.[player #], A.total, (Select Count(*) From 

(SELECT [Player #], Player, Handicap, Sunday, Monday, IIf([Monday] Is Null,0,([Monday]-[Handicap])-72) AS [Mon +/-], Tuesday, IIf([Tuesday] Is Null,0,([Tuesday]-[Handicap])-72) AS [Tues +/-], Wednesday, IIf([Wednesday] Is Null,0,([Wednesday]-[Handicap])-72) AS [Wed +/-], Thursday, IIf([Thursday] Is Null,0,([Thursday]-[Handicap])-72) AS [Thurs +/-], Friday, IIf([Friday] Is Null,0,([Friday]-[Handicap])-72) AS [Fri +/-], [Sunday $], [Monday $], [Tuesday $], [Wednesday $], [Thursday $], [Friday $], [Mon +/-]+[Tues +/-]+[Wed +/-]+[Thurs +/-]+[Fri +/-] AS Total
From [Player Information]
) as x

WHERE (10000*x.total+X.[player #]) <= (10000*A.total + A.[player #]) ) As rank

FROM 
(
SELECT [Player #], Player, Handicap, Sunday, Monday, IIf([Monday] Is Null,0,([Monday]-[Handicap])-72) AS [Mon +/-], Tuesday, IIf([Tuesday] Is Null,0,([Tuesday]-[Handicap])-72) AS [Tues +/-], Wednesday, IIf([Wednesday] Is Null,0,([Wednesday]-[Handicap])-72) AS [Wed +/-], Thursday, IIf([Thursday] Is Null,0,([Thursday]-[Handicap])-72) AS [Thurs +/-], Friday, IIf([Friday] Is Null,0,([Friday]-[Handicap])-72) AS [Fri +/-], [Sunday $], [Monday $], [Tuesday $], [Wednesday $], [Thursday $], [Friday $], [Mon +/-]+[Tues +/-]+[Wed +/-]+[Thurs +/-]+[Fri +/-] AS Total
From [Player Information]
) as A

) As XX

Order by 3

 

[pwbrown]

Almost there, this is great!!! The last thing is transfering the ranks into a pre-made table. Since the final pairing is done by ranking, everything such as times and amounts are already there, all that has to be done is to load the players.
I have created a query (select) that has the info that needs to go over and it includes the ranking. What I need to do is update the table tblFriday with the following fields from the query... Player & Handicap.

So in tblfriday the line that is ranked 1, the info from the query for ranking 1 will update.

At first, I thought of an update query, but I can not seem to find how to tell it that there is an order and order by rank.

Here is the current query code:

SELECT [Player Information].[Player #], [Player Information].Player, [Player Information].Handicap, Scores2.rank
FROM [Player Information] INNER JOIN Scores2 ON [Player Information].[Player #] = Scores2.[player #];

Once the table gets updated in order, this database is done! Unless they turn around and want more, but I would hope this would satisfy them for awhile.

 

[Golom]

UPDATE tblFriday As F INNER JOIN 

       (SELECT P.[Player #], P.Player, P.Handicap, S.rank
        FROM [Player Information] P INNER JOIN Scores2 S
              ON P.[Player #] = S.[player #]) As R

   ON F.rank = R.Rank
      
SET F.[Player #] = R.[Player #], 

    F.Handicap   = R.Handicap

I'm assuming that your tblFriday contains a field called "Rank". We simply match it with the same rank from your query and then copy the [Player #] and [Handicap] fields to tblFriday.

 

[pwbrown]

Only one problem... when it updates it updates the field [player] in the tblFriday with the players number and not their name. I understand that player # was used earlier to make a unique value, but when I try to add player I keep getting syntax errors.

 

[Golom]

This doesn't work?

SET F.[Player #] = R.[Player #], 

    F.Handicap   = R.Handicap,

    F.Player     = R.Player

Of course the fields [Player #], [Handicap] and [Player] must exist in tblFriday.

 

[pwbrown]

That just erased all the players from the database. The Handicaps are still in the player information table, but the player names have been deleted.

Here is the code that was used:

UPDATE tblFriday AS F INNER JOIN [SELECT P.[Player #], P.Player, P.Handicap, S.rank
        FROM [Player Information] P INNER JOIN Scores2 S
              ON P.[Player #] = S.[player #]]. AS R ON F.Rank = R.rank SET F.Player = [R].[Player], F.Handicap = R.Handicap;

I have re-entered the players in the player information table.

 

[Golom]

Something here does not compute. The SQL as shown makes no changes to the table [Player Information] ... only to the table tblFriday. Just to make sure that it is this statement that's causing the loss of information, do the following

Run this query

SELECT P.[Player #], P.Player, P.Handicap, S.rank [COLOR=red]INTO TempTable[/color]
FROM [Player Information] P INNER JOIN Scores2 S
     ON P.[Player #] = S.[player #]

and then run this

UPDATE tblFriday AS F INNER JOIN TempTable AS R 
       ON F.Rank = R.rank 

SET F.Player = R.Player, F.Handicap = R.Handicap;