Assigning Ranks

Creating tables and queries

Assigning Ranks

Postby evil44 » Thu Jan 21, 2016 6:03 pm

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
User avatar
evil44
 
Posts: 4
Joined: Thu Jan 21, 2016 3:09 pm

Re: Assigning Ranks

Postby eremmel » Thu Jan 21, 2016 7:31 pm

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   Expand viewCollapse view
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
eremmel
 
Posts: 1041
Joined: Tue Dec 30, 2008 1:15 am

Re: Assigning Ranks

Postby evil44 » Thu Jan 21, 2016 8:47 pm

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
User avatar
evil44
 
Posts: 4
Joined: Thu Jan 21, 2016 3:09 pm

Re: Assigning Ranks

Postby eremmel » Thu Jan 21, 2016 10:40 pm

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
eremmel
 
Posts: 1041
Joined: Tue Dec 30, 2008 1:15 am

Re: Assigning Ranks

Postby evil44 » Fri Jan 22, 2016 1:29 pm

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
evil44
 
Posts: 4
Joined: Thu Jan 21, 2016 3:09 pm

Re: Assigning Ranks

Postby Villeroy » Fri Jan 22, 2016 2:43 pm

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, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27707
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 1 guest