I have a DB column named ask_code and ask_grouping which says if the ask_code is grouping or not (like a boolean for G = yes). How I do a SELECT query to get all the ask_code by grouping?

Something like that:

| ask_code | ask_grouping | reference_id |
|   A1     |              |       1      |
|   TOTAL  |       G      |       1      |
|   AREA   |       G      |       1      |
|POPULATION|       G      |       1      |
|   A2     |              |       2      |
|   TOTAL  |       G      |       2      |
|   AREA   |       G      |       2      |

And I want:

| ask_code |   grouping   |
|   A1     |   TOTAL      |
|   A1     |   AREA       |
|   A1     |   POPULATION |
|   A2     |   TOTAL      |
|   A2     |   AREA       |

CodePudding user response：

There is no ordering defined, so the output is not in the desired order:

SELECT 
  m1.reference_id,
  m1.ask_code,
  m2.ask_code as ask_gropuing
FROM mytable m1
INNER JOIN mytable m2 ON m2.ask_grouping='G' and m2.reference_id=m1.reference_id
WHERE m1.ask_grouping is null
ORDER BY m1.reference_if,m1.ask_code;

output:

I just left reference_id in the output, but that can be left out. Still the ordering is unknown, so possibly incorrect.

see: DBFIDDLE

CodePudding user response：

You may try with max window function as the following:

select grp as ask_code, ask_code as grouping_
from
(
  select ask_code, ask_grouping, reference_id,
     max(case when ask_grouping is null then ask_code end) over (partition by reference_id) as grp
  from table_name
) T
where ask_grouping='G'
order by grp, ask_grouping

see demo

CodePudding user response：

You can get your result by just following query:

select ask_code, type 
from test 
group by concat(ask_code,type);

sqlfiddle