Assigning Ranks

Creating tables and queries
Post Reply
User avatar
evil44
Posts: 4
Joined: Thu Jan 21, 2016 3:09 pm

Assigning Ranks

Post by evil44 »

I have a Table titled Teams_Table with fields called Team_ID (primary key, short text) and Amaze_Score (number). I need to be able to assign a rank (1 through 10) based on Amaze_Score. There are 50 different teams, but I just want the top 10 and to stratify them 1st through 10th. If it's possible to add a column in the same table with that ranking value, that would be great.

I assumed this would be done via a query but I can't seem to get the syntax to work properly. Any help would be appreciated.

Thanks,
Dr. Evil
Office 2013
Windows 7
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: Assigning Ranks

Post by eremmel »

Making a query with ranks is not so difficult. But you need to deside what to do with equal ranks.
Having a database like oracle, MSSQL server, DB2 and Postgress, its just part of the syntax. With other database you have to self join the table.
Here a layout (Fix the needed quoting yourself)

Code: Select all

SELECT COUNT(*) as "rank", a.Team_ID, a.Amaze_Score
FROM Teams_Table a LEFT JOIN Teams_Table b 
    ON a.Amaze_Score <= b.Amaze_Score
GROUP BY a.Team_ID, a.Amaze_Score
HAVING COUNT(*) <= 10
ORDER BY 1
With equal scores you will see two teams with same rank. You might add other criteria to fine grain e.g.
ON a.Amaze_Score < b.Amaze_Score OR a.Amaze_Score = b.Amaze_Score AND a.Team_ID <= b.Team_ID

Good Phd.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
User avatar
evil44
Posts: 4
Joined: Thu Jan 21, 2016 3:09 pm

Re: Assigning Ranks

Post by evil44 »

Good stuff, Good PhD, but where do I input that layout? I'm using Access 2013. I don't have super in-depth knowledge of Access and am slowly working my way through it.

As for equal scores, it's fine to give them equal ranks....like 1, 2, 2, 4, 5.
Office 2013
Windows 7
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: Assigning Ranks

Post by eremmel »

In Base you have a section where you can save queries. So you can create a new query and save it there.
You need to do some studie on how to use Base. Base is one of the more technical tools that need your study time before you can use it to make things happen.
On the forum you can find tutorials and also on YouTube you can find many short movies that teach you all kind of aspects.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
User avatar
evil44
Posts: 4
Joined: Thu Jan 21, 2016 3:09 pm

Re: Assigning Ranks

Post by evil44 »

Now you lost me. I'm using Access 2013 because that's the program that my employer allows on my computer. Can anyone out there help with Access 2013 syntax for this issue?
Office 2013
Windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Assigning Ranks

Post by Villeroy »

Everybody here uses the Base component of either OpenOffice or LibreOffice. You are on the wrong forum.
http://openoffice.org/
http://libreoffice.org/
Nevertheless, eremmel's SQL statement should work with MS Access as well (if you know how to do anything non-trivial with that development tool).
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply