Mysql - ranking based on occurrence of a value

Question

I have a table in MySQL database to which records get added continuously.I want to rank the occurrence of a value and save it to a new field.

I want to populate the FruitRank field, based on the occurrence of the fruit, for that particular Person/Name.

Here is the expected table - Name and Fruit get added to the table in real time. FruitRank field should be calculated and updated in real time. How to go about this?

Name | Fruit | FruitRank(new field)

Amy | apple | 1

Amy | orange| 2

Tom | grapes | 1

Amy | kiwi | 3

score 1 · Answer 1 · edited May 23 '17 at 12:00

Here are two possible approaches, at the database level, depending on your needs:

If there's a reason to store a record in the database for every occurrence of a Person/Fruit (i.e. You need to save the time the fruit was eaten, etc.), then there is no reason to store the rank value in the database, as that would require an UPDATE with each INSERT. You can get the rank with a simple query using COUNT(*).
If there is no reason to store every occurrence, then you should have only one entry per Person/Fruit combination with a rank value which is updated on every subsequent occurrence.

Rank Retrieved with Aggregate Function

Query to get the rank: (Assuming every occurrence is stored in Person_Fruit table)

SELECT person, fruit, COUNT(*)
FROM person_fruit
WHERE person = 'the_person'
  AND fruit = 'the_fruit'
GROUP BY 1, 2;

Rank Stored in Database

Assuming table Fruits (id, person, fruit, rank), with a multi-column index on person and fruit, so you have only one occurrence of each unique combination.

Prior to INSERT/UPDATE, check if Person/Fruit already exists:

SELECT id
FROM fruits
WHERE name = 'the_name' AND fruit = 'the_fruit';

If it doesn't, INSERT Person/Fruit with rank value of 1, as this is the first occurrence:

INSERT INTO fruits (id, person, fruit, rank)
VALUES (NULL, 'the_person', 'the_fruit', 1);  /* NULL should be replaced by auto-generated value, if set up for that */

If it does, UPDATE the rank:

UPDATE fruits 
SET rank = rank +1
WHERE id = id        /* You can use id returned from previous `SELECT`, or Person AND Fruit */

score 0 · Answer 2 · answered Feb 14 '17 at 22:13

You can write an AFTER INSERT trigger on current table that calculates fruit rank and inserts the records into another table. You can use the following query to calculate the rank:

SELECT COUNT(*) into rank
FROM table 
WHERE person NEW.person and fruit = NEW.fruit;

Once you get the rank, you can execute the INSERT query to insert the records into another table.

Here's an example of AFTER INSERT trigger.

score 0 · Answer 3 · answered Feb 14 '17 at 22:38

From your sample data (the only available way to somehow define what table structure you have in mind) it is not clear why should you retain different records with absolutely same payload.

It basically seems that the only thing you're updating - is rank.

So in this case, the rank come naturally with the updates like (rather than inserts):

UPDATE fruitRanks SET FruitRank=FruitRank+1 WHERE Fruit = 'apple' AND Name='Amy';

If you do have difference in your payloads - then use the separate table (in conjunction with AFTER UPDATE trigger), there's no need to retain the Rank in each and every row then.

Or just drop the field and calculate it whenever you need it with grouping & aggregation function.

Mysql - ranking based on occurrence of a value

3 Answers3

Rank Retrieved with Aggregate Function

Rank Stored in Database