0

I have a MariaDB table

id | key  | year | .... | position        
1  | key1 | 2004 | ... |  3   
2  | key1 | 2011 | ... |  2  
3  | key1 | 2017 | ... |  1    
4  | key2 | 1994 | ... |  2 
5  | key2 | 2004 | ... |  1    
6  | key3 | 2004 | ... |  1    
7  | key4 | 1980 | ... |  2 
8  | key4 | 2011 | ... |  1

Position is the row number within they Key field order by year descending

How can I create a trigger to autofill position when I insert, delete or update any row. thanks

2
  • I currently doing in select with ROW_NUMBER() OVER (PARTITION BY key ORDER BY year desc, id desc ) as xposition but I have to do it each time
    – jork
    Jan 11, 2020 at 12:29
  • @jork, true but is that a problem? If you have 100 key_x and delete the first one you will have to update 99 additional rows Jan 11, 2020 at 13:02

1 Answer 1

1

If you are running MySQL8.0, you could drop the column position, and create a view like this:

CREATE VIEW view_amariadbtable AS 
SELECT 
  id, 
  `key`, 
  year, 
  RANK() OVER (PARTITION BY year ORDER BY year DESC,id DESC) as position 
FROM amariadbtable

BTW, you should NOT use reserved words as column name (key)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.