1

I have a database in mysql which has three rows, these rows has concatenated multiples values(values separated by a comma) already in it. I want to strike the rank using find_in_set function or any better function to get the positions. 

   Table
   id      |    NUMBERS  |
    1      |   30,40,10  |
    2      |   58,29,21  |
    3      |   18,25,51  |

I want to rank each row in this format

   id      |    NUMBERS  |  POSITION  |
    1      |   30,40,10  |  2,1,3     |
    2      |   58,29,21  |  1,2,3     |
    3      |   18,25,51  |  3,2,1     |

I Know the data representation and structure is wrong, but the data i have currently is made like the above and has a lot of data in it, meaning changing the structure would take me a lot of time, although I would change it later.

I need a workaround idea as to how to do this. I would be grateful for your support thanks.

MessiahCoder
  • 111
  • 6
  • 1
    Moving forward, normalize your data. See [Junction Tables](http://stackoverflow.com/a/32620163). They use indexes and perform (your code won't). – Drew Sep 27 '16 at 17:09
  • There's nothing built into MySQL to split up a comma-separated list. You can use `FIND_IN_SET` to search for a specific value in a list, but if you want to split it up you need to use a stored function or procedure. – Barmar Sep 27 '16 at 17:48
  • You can also use `SUBSTRING_INDEX()` to get the Nth item in the list. But there's no way to loop over an arbitrary number of items. Are there always exactly 3 items? – Barmar Sep 27 '16 at 17:50
  • @Barmar No. they can be more than 3 – MessiahCoder Sep 27 '16 at 22:09

0 Answers0