mysql group by 大小写_MySQL - 边界大小写使用GROUP BY

My understanding of GROUP BY is that its standard use is to aggregate items. So a typical example might be:

select

count(id),

department,

from table

group by department

The above would a count of all id's per department.

So, I got taught a very useful (but possible pretty dodgy!) trick using group by. I was wondering if this usage has any problems. Although the query runs as expected [results as expected in all cases], my spidey sense is tingling a bit...

Imagine the following data set:

id | user_id | cost | note

----------------------------------

1 1 120 Test 1

2 1 150 Test 2

3 2 100 Test 3

4 3 120 Test 4

Now if we do the following SQL:

select * from table

group by user_id

You get the following result set.

id | user_id | cost | note

----------------------------------

1 1 120 Test 1

3 2 100 Test 3

4 3 120 Test 4

The query runs apparently as follows:

run through the table

when a groupable user id is found, ignore the subsequent ones

return this table of unique user_id items

Effectively I get a "unique", with specific boundaries and I am able to select * from this list.

Furthermore, by ordering the table prior to the order by, I can use this to filter all costs.

So - this is also as you'd expect.. BUT:

In the ABOVE example - Say I actually ensured that for user_id 1, the value 120 was shown (as opposed to it's other possible values - 150 in this case). Then 120 seems to be guaranteed to be the response.

The approach could be then to sort by some order alphabetical/numeric/other advanced filters etc... THEN use this sort to force the first item in the table to be the "answer".

The actual query I want to do is pretty complex. Using MIN or similar are not suitable for the end value I want... However: this "order your table then take the first unique item using group by" approach is actually quite elegant (I think).

I am actually using group by constrained across 4 fields, and this, combined with other SQL makes a CORRECT answer.

So. After that long background: a question!

All documentation I have used only talks about using group by with aggregate functions. I can't seem to find the behaviour of JUST group by.

This strikes me as one of two things:

a correct (mis)use case that's not been documented

an accidental behaviour of whichever version of mySQL I'm using.

So... which one is it?

If it's a correct, but edge case, behaviour, then great. If I'm tricking the SQL engine to spit something out, then I've got no proof this is compatible with future versions so I'd be uneasy to use it.

Cheers in advance all.

解决方案

After looking into this through the above links/help, I think it's unfortunately the case that: while the answer is correct, it's not guaranteed to be correct... More accurately it is "indeterminate".

I am genuinely confident following my repeated successful use of this that the internal workings are "first come first show", but as the spec also says this isn't guaranteed so I can't rely on it.

Cheers for help all. Have up-voted all comments.