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

Improve this question
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
    – Lennart - Slava Ukraini
    Jan 11, 2020 at 13:02

1 Answer 1

Reset to default
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)

Improve this answer

Your Answer

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

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