show all data only group by specific rows : Select * from table group by column having column = 'value'

Question

I use mysql. My table look like this:

Last I try to use this query

SELECT * FROM movie GROUP BY `group`  HAVING cateogry = 'TV'

I want with this query result as: show all but only GROUP BY TV category, where category = 'TV'

I want this Result

But my query give me this result (HAVING in query work as WHERE clouse)

IF I use this QUERY

SELECT * FROM movie GROUP BY `group`

It give me this result

I want -> QUERY -> GROUP BY group (ID no 9 and ID 1,2,3 treat as different group name)

IF group has all same values BUT category='movie' (RETURN ALL ROWS group by NOT APPLY)

IF group has all same values BUT category='TV' (RETURN 1 ROW group by APPLY)

Solution 1

SELECT min(ID) as ID, min(Name), `group`, Category
FROM movie
GROUP BY `group`, Category
ORDER BY ID

Solution 2

You seem to want this query:

select m.*
from movie m join
     (select `group`, min(id) as minid
      from movie
      group by `group`
     ) g
     on m.id = g.minid;

Comments

[user2511667]

Thanks for your help, but this is not my answer,, Please check my first image with arrows, and i want 2nd image result,, now please tell me, what query you use to get 2nd image result,.. AND Best if you use GROUP by group with your query...

[Gordon Linoff]

@user2511667 . . . I did and have again looked at your image and that is what this query should return.

[user2511667]

with your query ID 9 not showing,, i change your query with where clouse but still not working,,,, You query result same as LAST image

[user2511667]

Thank you soo much,, I change little bit in your query and i get my result... Thanks, :* what is change in query see my ANSWER

[user2511667]

NO, sorry its not working,, i check properly,,if i use where clause and remove ON m.id = g.minid all repeat result show,,if i use then ID 9 not showing,,

[Gordon Linoff]

@user2511667 . . . As a note: I don't believe this really works in general. This returns the minimum id and the minimum name -- but not the name for the minimum id. You seem to want the row with the minimum id (which is what my answer does), not two separate minimums.

[Gordon Linoff]

@user2511667 . . . If you want both group and category for the "groups", then they both need to be included in the group by clause.

[user2511667]

@Gordon Linoff You are right, but i dont want minimum value, my result is, SELECT * FROM movie GROUP BY group, Category

[mickmackusa]

Please add some explanation to this answer.