0

I have two columns "TOPSALES" and "TOPCOUNTRY"

TOPSALES Containing a number separated by commas like this : 

10,30,83

10 mean 10 sales in USA

30 mean 30 sales in CANADA

83 mean 83 sales in UK

I have second column "TOPCOUNTRY" = NULL.

I want get max number in TOPSALES field and Insert their country name into TOPCOUNTRY using variable or somthing like this.

My Tables Right Now:

TOPSALES   |  TOPCOUNTRY
10,30,83   |  NULL

Desired Ouput :

TOPSALES   |  TOPCOUNTRY
10,30,83   |  UK
Mostafa Solati
  • 1,235
  • 2
  • 13
  • 33
DavidBoe
  • 17
  • 9
  • 3
    See [is-storing-a-delimited-list-in-a-database-column-really-that-bad](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) – Paul Spiegel Apr 27 '19 at 16:22
  • `83 mean 83 sales in UK`, where you are defining this? in another table? – Arulkumar Apr 27 '19 at 16:23
  • @Arulkumar This information is not on Database, it me who made this arrangement its only in my mind. – DavidBoe Apr 27 '19 at 16:28
  • As if storing delimited lists wasn't bad enough you also want to materialize data that can be calculated from dependent data. Don't do this. Create a table instead where you list the country codes together with an integer showing their rank. Or possibly even have a view if this is also data that can be calculated from other data. To get the top country join the country table order by the rank descending and use `LIMIT 1`. – sticky bit Apr 27 '19 at 16:29
  • 2
    If it's a new project, then change the design right now. – Paul Spiegel Apr 27 '19 at 16:30
  • this data of TOPSALES is not necessary for updates, i want do this one time for ever, any solutions please ! – DavidBoe Apr 27 '19 at 16:35
  • If it's used only one time per year that's even a stronger argument to do it normalized as the possible small performance gain (not from the delimited list, that's rather a break but from materializing calculations) hasn't any weight then. – sticky bit Apr 27 '19 at 16:38
  • Are you using MySQL or MariaDB?. What version exactly?. In case you can not change the design and you are using MySQL 8.0.4 or higher, you can try something like [dbfiddle](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=394694cb48b5fcb46c33ae756bd78669). – wchiquito Apr 28 '19 at 14:06

0 Answers0