SQL grouping vs. sort

First of all this is the rows/data inside my table group_student

g_id |user_id |group_no |is_leader
4   1   3   0
3   9   5   0
5   10  5   0
6   11  1   0

I'm having a bit of problem of grouping the rows in my table. Right now I'm using this script.

SELECT *
FROM (groups_studen开发者_运维技巧t)
group by group_no

and it's giving me this result:

g_id |user_id |group_no |is_leader
6   11  1   0
4   1   3   0
3   9   5   0

The g_id 5 is missing. I thought if you used group by it will segregate the rows by their group_no. Adding another column field in my group_by would do it but it will mess up my view since I am using a foreach loop. How can I show the g_id 5 and also have it grouped.

When you do an SQL grouping, all rows with fields that match the group specification COLLAPSE into a single row. Generally the value that's returned for that grouping is the first value encountered, which in your case is the g_id = 3.

If you want to retrieve all the rows, with the "group" values together, you should use a SORT clause instead. That'd return all rows, with the sort-by values "grouped" together.

Grouping in SQL is for when you want to use the aggregate functions (sum, count, average, etc...) across the members of a group. Sorting is for when you don't want to aggregate things, but keep the similar records together.

As your query doesn't have an aggregate function in it, the group clause is acting as more of a "select distinct" instead.

What exactly is it that you're wanting to get out of the query? To get a better idea of how GROUP BY works, do this:

SELECT GROUP_CONCAT(g_id)
FROM (groups_student)
group by group_no

g_id 5 is only missing because it's "folded in."

Use ORDER BY instead of GROUP BY (in this case).
Read more about differences here.