Geek Answers Handbook

MAX unit inside the group

I have the following data

GROUP_ID INDEX NULL 1 1 2 NULL 3 1 4 NULL 5 2 6 2 7

I want to get a field containing:

  • If group_id is NOT NO, the smallest index in this group
  • if group_id IS NULL, then the index from this row

A result data set for the above example:

  GROUP_ID INDEX GROUP_INDEX_MIN NULL 1 1 1 2 2 NULL 3 3 1 4 2 NULL 5 5 2 6 6 2 7 6

In Oracle, I would solve this with (CASE WHEN group_id IS NOT NULL THEN MIN(index) OVER (PARTITION BY group_id) ELSE group_id END) , but since MySQL does not support this, I really don't know how to proceed :) I can solve this problem using a subquery that retrieves the minimum values ​​for each group and then left by joining it, but I think there should be a more elegant solution.

If you need more information, please ask. Thanks.

+4
source share
5 answers

Just create an inline view representing min Group_index_min (minT) and then join it and merge between group_ID and min

 SELECT t.group_id, t.index, Coalesce(t.group_id, minTgroup_index_min) group_index_min FROM yourtable t LEFT JOIN (SELECT group_id, MIN(index) group_index_min FROM yourtable GROUP BY group_id) minT ON t.group_id = minT.group_id
+4
source

Just in case, you want to find the minimum INDEX values ​​for each group, without having to display the entire table and thus repeat the non-NULL GROUP_ID s, you can try the following query:

 SELECT GROUP_ID, MIN(`INDEX`) AS GROUP_INDEX_MIN FROM atable GROUP BY GROUP_ID, CASE WHEN GROUP_ID IS NULL THEN `INDEX` ELSE GROUP_ID END

It should return something like this:

 GROUP_ID GROUP_INDEX_MIN -------- --------------- NULL 1 NULL 3 NULL 5 1 2 2 6

That is, as I said, suppresses repetition of values ​​other than NULL GROUP_ID .

+1
source

I think I found a solution:

 SELECT a.group_id, a.index, (CASE WHEN a.group_id IS NOT NULL THEN a.group_index_min ELSE a.index END) group_index_min FROM ( SELECT p.group_id, p.index, (CASE WHEN @group <> IFNULL(p.group_id, 0) THEN @n := p.index ELSE @n := @n END) group_index_min, @group := IFNULL(p.group_id, 0) FROM temp p ORDER BY p.group_id, p.index ) A ORDER BY (CASE WHEN a.group_id IS NOT NULL THEN a.group_index_min ELSE a.index END)

This works for my example, sorting values ​​by their index (if they do not belong to the group) or by the index of the group (if they belong to the group). Now try applying to my real tables / data :)

If someone wants to see this and say if it contains some of MySQL DONT, I would appreciate it. As I said, I'm not a MySQL expert, so I'm not sure if this is the best way to solve this problem.

0
source

One more attempt:

 SELECT GROUP_ID , `INDEX` , COALESCE( ( SELECT MIN(ti.GROUP_INDEX_MIN) FROM TableX AS ti WHERE ti.GROUP_ID = t.GROUP_ID ) , t.GROUP_INDEX_MIN ) AS GROUP_INDEX_MIN FROM TableX AS t ORDER BY GROUP_INDEX_MIN
0
source

The combined "PreQuery" is performed for all groups, including for NULL records. Thus, a left connection is not even required. However, using “CASE / WHEN” will still see the “NULL” record from the original source and use it as the basis for getting MinIndex (if not null) or just use your own index position (when it was zero)

 select YT.Group_ID, YT.`Index`, CASE WHEN YT.GROUP_ID IS NULL THEN YT.`Index` ELSE YT2.MinIndex END as Group_Index_Min from YourTable YT JOIN ( select YT2.Group_ID, MIN( YT2.`Index` ) MinIndex from YourTable YT2 group by YT2.Group_ID ) YTMin on YT.Group_ID = YTMin.Group_ID
0
source

More articles:


All Articles