Resolved: Group by Id and by alias?

Question:

I have a table with repeated Ids and with a JSON data that represents an alias “players_level”, I’m trying to group the Ids so I don’t have duplicated Ids but the problem is that when grouping the Ids I can’t get the row with the greater “players_level” alias, example I have this data:

id	score	players	players_level
1	0	{}	0
16	1000	{“1”: {“222”: [], “1232”: [], “2824”: [], “33332”: [], “66345”: []}}	2
17	0	{“1”: {}}	1
16	500	{“1”: {“2824”: [“148500”]}, “48”: {“2890”: []}}	3

And I’m trying to group the data like this:

id	score	players	players_level
1	0	{}	0
17	0	{“1”: {}}	1
16	500	{“1”: {“2824”: [“148500”]}, “48”: {“2890”: []}}	3

As you can see the row 2 disappeared because its players_level was 2 and the fourth one has level 3… but when trying to group using multiple techniques with SQL (Group By, Order By, Max…) I can’t get the right row to output 🙁
Can anybody help me please? I have a fiddle link with the data example:
https://www.db-fiddle.com/f/sfQVa722kfkNw2JyRdQJZr/0

Answer:

You can use HAVING with a subselect with MAX
Schema (MySQL v5.7)

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
CREATE TABLE IF NOT EXISTS `test` (
  `id` int(6) unsigned NOT NULL,
  `score` int(6) unsigned NOT NULL,
  `players` varchar(200) NOT NULL
) DEFAULT CHARSET=utf8;
INSERT INTO `test` (`id`, `score`, `players`) VALUES
  ('1', 0, '{}'),
  ('16', 1000, '{"1": {"222": [], "1232": [], "2824": [], "33332": [], "66345": []}}'),
  ('17', 0, '{"1": {}}'),
  ('16', 500, '{"1": {"2824": ["148500"]}, "48": {"2890": []}}')

Query #1

SELECT t.id, t.score, t.players,
  (CASE
  WHEN (JSON_CONTAINS(JSON_EXTRACT(t.players, '$."1"."2824"'), '148500') = 1 OR JSON_CONTAINS(JSON_EXTRACT(t.players, '$."1"."2824"'), '"148500"') = 1) THEN 3
  WHEN JSON_CONTAINS(JSON_KEYS(JSON_EXTRACT(t.players, '$."1"')), '"2824"') = 1 THEN 2
  WHEN JSON_CONTAINS(JSON_KEYS(t.players), '"1"') = 1 THEN 1
  ELSE 0
  END) players_level
from test t  
HAVING (id,players_level) IN (SELECT t.id,
  MAX(CASE
  WHEN (JSON_CONTAINS(JSON_EXTRACT(t.players, '$."1"."2824"'), '148500') = 1 OR JSON_CONTAINS(JSON_EXTRACT(t.players, '$."1"."2824"'), '"148500"') = 1) THEN 3
  WHEN JSON_CONTAINS(JSON_KEYS(JSON_EXTRACT(t.players, '$."1"')), '"2824"') = 1 THEN 2
  WHEN JSON_CONTAINS(JSON_KEYS(t.players), '"1"') = 1 THEN 1
  ELSE 0
  END) players_level
from test t
GROUP BY t.id);

id	score	players	players_level
1	0	{}	0
17	0	{“1”: {}}	1
16	500	{“1”: {“2824”: [“148500”]}, “48”: {“2890”: []}}	3

View on DB Fiddle

If you have better answer, please add a comment about this, thank you!

Resolved: Group by Id and by alias?

Resolved: When converting markdown to latex with pandoc, how can I end a block before the next heading?

Resolved: TYPO3 SQL error: Field ‘tx_imagezoom_set’ doesn’t have a default value

Resolved: std::regex_replace to replace multiple combinations

Leave A Reply