0

I have a table like the following:

group_id     candidates_id      score
1            (a, b, c,g)       (0,1,0,1)
2            (d,e,h)           (1,0,0)

I want to create a list column named top_candidate where in each group, the only the top2 candidates with the lower scores (only 0, 1) are selected. If there is a tie, retain them all.

The desired table will be:

group_id     candidates_id      score       top_candidate
1            (a, b, c,g)       (0,1,0,1)     (a,c)
2            (d,e,h)           (1,0,0)       (e,h)

What would be some possible ways to achieve the desired result. I thought of melting the list but then I was not sure how to track the group level info (each group has its own candidates_id and the sorting by score is done at the level of group_id.

lll
  • 1,049
  • 2
  • 13
  • 39
  • please take a look at https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad i see only the option to spllit your tuples and check each value – nbk Mar 26 '21 at 20:38
  • whats data type for candidates_id and score? – eshirvana Mar 26 '21 at 20:39
  • I don't think this question is duplicated. I agree this data schema is deficient, but sometimes we have to deal with this kind of problems. I have a solution [dbfiddle](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=89d86023a46f90e32820f6161638e60d). First I "normalize" the table (with a temporary result) and then get the top candidates with score 0. – nachospiu Mar 29 '21 at 18:50

0 Answers0