0

I have a target where I have this DUPLICATE checker with COUNT for multiple arrays. As we can see. Any suggestions on how I make this work? Thank you and have a nice day.

DATA:

id value
1 [5,6,8,4,2]
2 [2,3,4,1,8]
3 [9,3,2,1,10]

Normal result:

number count
1 2
2 3
3 2
4 2
5 1
6 1
7 0
8 2
9 1
10 1

This is my target result with sorting (Highest count):

number count
2 3
1 2
3 2
4 2
8 2
5 1
6 1
10 1
9 1
Luuk
  • 12,245
  • 5
  • 22
  • 33
Dark
  • 132
  • 1
  • 15
  • What is the _type_ of the `value` column? – Tim Biegeleisen Dec 23 '22 at 10:28
  • Hello @TimBiegeleisen. Varchar 255. Thank you – Dark Dec 23 '22 at 10:30
  • 1
    I suggest that you stop storing your data this way. – Tim Biegeleisen Dec 23 '22 at 10:31
  • Do you have any better approach? I'm willing to learn and apply it. Thank you @TimBiegeleisen – Dark Dec 23 '22 at 10:33
  • A dozen lines of PHP could do this. Do you need it all to be SQL? – Simon Goater Dec 23 '22 at 10:43
  • Normal Result? Does that mean you have code which splits the arrays? – P.Salmon Dec 23 '22 at 10:43
  • @SimonGoater Yes, if possible. Thank you – Dark Dec 23 '22 at 10:49
  • @P.Salmon Currently, I'm working on the SPLIT. But if you have any suggestion on how can I improve my target/codes, that'd be much appreciated. – Dark Dec 23 '22 at 10:49
  • see: https://stackoverflow.com/a/56597424/724039 and how to use it with this data: [DBFIDDLE](https://dbfiddle.uk/_nrQSu7L) – Luuk Dec 23 '22 at 10:52
  • 1
    about "storing your data this way": [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) – Luuk Dec 23 '22 at 10:54
  • @Luuk Hello, thank you for these. I can easily do this https://prnt.sc/VD0IJOd_vpRW But i'm just worrying since it might reach millions in a future so rather than storing the number per row, i stored it as an array. Can you enlighten me regarding my question? Do you think it's okay even it reaches a lots of rows? – Dark Dec 23 '22 at 10:58
  • @Luuk I'm worried also that my query might take too long if i proceed on that process because it has too many rows if i split it. – Dark Dec 23 '22 at 11:03
  • The question has "Any suggestions on how I make this work?" Not "will this work in the future". This other question is answered here: [Why Premature Optimization Is the Root of All Evil](https://stackify.com/premature-optimization-evil/) (and on other places on the internet) – Luuk Dec 23 '22 at 11:05
  • Got it. Thank you so much. I have learned so many things today. Really Appreciated it. – Dark Dec 23 '22 at 11:08
  • 1
    Final comment, you can also create some data and test of it behave bad. An example of adding a longer record is here: https://dbfiddle.uk/2PiS10JN (questions/comments about this should go in another question, not as follow-up here) – Luuk Dec 23 '22 at 11:21
  • Got it. You might want to post your answer. Will gladly accept it as a correct answer. Thank you – Dark Dec 23 '22 at 11:26

0 Answers0