Urgent! Auto Numbering

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

First, let me say I know this has been asked and answered many, many times.
I have tried out several of the solutions given, and can't seem to make it
work for me. Please Help!

I have a table: 5DailyPlayers
I have a query: 12SortDailyWinners

With this query I am sorting the Scores descending, and pulling the top 30
records. I am putting the results in a new table: 13DailySort. I am using
the following fields: Date, PrizeOrder, Account Number, Score, Prize. The
Date, Account Number and Score are being pulled from 5DailyPlayers. I need
to put numbers 1-30 in the PrizeOrder field. Please tell me how I can do
this. I want to auto number thru the query.

Here is my existing SQL:
SELECT TOP 30 [5DailyPlayers].Date, [5DailyPlayers].PrizeOrder,
[5DailyPlayers].[Account Number], [5DailyPlayers].Score,
[5DailyPlayers].Prize INTO 13DailySort
FROM 5DailyPlayers
WHERE ((([5DailyPlayers].Status) Is Null))
ORDER BY [5DailyPlayers].Score DESC;
 
Try this --
SELECT TOP 30 Q.PlayDate, Q.PrizeOrder, Q.[Account Number], Q.Score,
Q.Prize, (SELECT COUNT(*) FROM [5DailyPlayers] Q1
WHERE Q1.Score < Q.Score)+1 AS Rank
FROM [5DailyPlayers] AS Q
ORDER BY Q.Score;
 
Karl! It works! But I need a a little tweaking, please. I need the score
to be Descending and the rank to stay Ascending. So the Highest Score = 1
and the Lowest Scrore = 30. Also, I need the numbers to go 1, 2, 3, 4, 5,
ect. regardless of tied scores...so maybe just numbering, not ranking? We
will have to deal with the tied scores manually, I think. The plan is to add
the prize amounts and divide equally. So, if you could please make the
scrore Descending and give me a column of straight numbering, I would really
appreciate it!!!

Sylvia

KARL DEWEY said:
Try this --
SELECT TOP 30 Q.PlayDate, Q.PrizeOrder, Q.[Account Number], Q.Score,
Q.Prize, (SELECT COUNT(*) FROM [5DailyPlayers] Q1
WHERE Q1.Score < Q.Score)+1 AS Rank
FROM [5DailyPlayers] AS Q
ORDER BY Q.Score;


Sylvia said:
First, let me say I know this has been asked and answered many, many times.
I have tried out several of the solutions given, and can't seem to make it
work for me. Please Help!

I have a table: 5DailyPlayers
I have a query: 12SortDailyWinners

With this query I am sorting the Scores descending, and pulling the top 30
records. I am putting the results in a new table: 13DailySort. I am using
the following fields: Date, PrizeOrder, Account Number, Score, Prize. The
Date, Account Number and Score are being pulled from 5DailyPlayers. I need
to put numbers 1-30 in the PrizeOrder field. Please tell me how I can do
this. I want to auto number thru the query.

Here is my existing SQL:
SELECT TOP 30 [5DailyPlayers].Date, [5DailyPlayers].PrizeOrder,
[5DailyPlayers].[Account Number], [5DailyPlayers].Score,
[5DailyPlayers].Prize INTO 13DailySort
FROM 5DailyPlayers
WHERE ((([5DailyPlayers].Status) Is Null))
ORDER BY [5DailyPlayers].Score DESC;
Click to expand...
Click to expand...
 
Back
Top