Question 21594 in Mysql Question posted in
The official MySQL documentation can be found here.

SQL subquery with multiple rows – Mysql

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       |
Tags:

2

Answers


  1. 0

    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:

    reference_id ask_code ask_gropuing
    1 A1 TOTAL
    1 A1 AREA
    1 A1 POPULATION
    2 A2 TOTAL
    2 A2 AREA

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

    see: DBFIDDLE

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search