Dan,
> Thanks to Rhino & Dan for the corrections, HAVING in that query makes it
> return only id values for which there is one row, and the header is
> wrong since "first row of every group" doesn't mean anything without
> explicit ordering.
/>What do you mean?/
In SELECT foo ... GROUP BY foo ..., which row, of a group of rows with
repeating foo values, is returned is "undetermined" (which means,
roughly, it's determined by current physical ordering), unless there's
an explicit within-group ordering as in your example. Here's a table of
five rows with repeating values of id:
SELECT * FROM chi;
+----------+----+------+
| child_id | id | type |
+----------+----+------+
| 1 | 1 | 0 |
| 2 | 1 | 0 |
| 3 | 1 | 1 |
| 4 | 1 | 2 |
| 5 | 2 | 2 |
+----------+----+------+
Make a copy of the table which physically reverses row order:
CREATE TABLE chi2
SELECT * FROM chi
ORDER BY child_id DESC;
SELECT * FROM chi2;
+----------+----+------+
| child_id | id | type |
+----------+----+------+
| 5 | 2 | 2 |
| 4 | 1 | 2 |
| 3 | 1 | 1 |
| 2 | 1 | 0 |
| 1 | 1 | 0 |
+----------+----+------+
The two tables have identical data and opposite physical ordering, so
SELECT ... GROUP BY id returns different rows:
SELECT * FROM chi GROUP BY id;
+----------+----+------+
| child_id | id | type |
+----------+----+------+
| 1 | 1 | 0 |
| 5 | 2 | 2 |
+----------+----+------+
SELECT * FROM chi2 GROUP BY id;
+----------+----+------+
| child_id | id | type |
+----------+----+------+
| 4 | 1 | 2 |
| 5 | 2 | 2 |
+----------+----+------+
2 rows in set (0.00 sec)
Applying your query to chi1 and chi2, though, returns identical results.
PB
-----
Dan Bolser wrote:
>On Mon, 11 Apr 2005, Peter Brawley wrote:
>
>
>
>>Thanks to Rhino & Dan for the corrections, HAVING in that query makes it
>>return only id values for which there is one row, and the header is
>>wrong since "first row of every group" doesn't mean anything without
>>explicit ordering.
>>
>>
>
>What do you mean?
>
>
>
>
>>PB
>>
>>-----
>>
>>Dan Bolser wrote:
>>
>>
>>
>>>On Mon, 11 Apr 2005, Rhino wrote:
>>>
>>>
>>>
>>>
>>>
>>>>----- Original Message -----
>>>>From: "Dan Bolser" <dmb@stripped>
>>>>To: <mysql@stripped>
>>>>Sent: Monday, April 11, 2005 5:58 AM
>>>>Subject: Display 1st row of every group?
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>>I read with great interest this
>>>>>
>>>>>http://www.artfulsoftware.com/queries.php#4
>>>>>
>>>>>Display 1st row of every group
>>>>>
>>>>>SELECT id
>>>>>
>>>>>
>>>>>FROM tbl
>>>>
>>>>
>>>>>GROUP BY id
>>>>>HAVING count(*) = 1;
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>Despite what the heading says for that query, it is NOT going to return
> the
>>>>first row of every group. In fact, it is going to return only groups that
>>>>contain one row. For example, given this data:
>>>>
>>>>ID
>>>>--
>>>>5
>>>>5
>>>>5
>>>>6
>>>>7
>>>>7
>>>>8
>>>>8
>>>>8
>>>>9
>>>>
>>>>That query will return return this:
>>>>
>>>>id
>>>>--
>>>>6
>>>>9
>>>>
>>>>In other words, it is returning only groups where there is a single ID
> with
>>>>that value. Note also that it is returning ONLY an ID, not the ID plus
> the
>>>>rest of the row. Their description of what the query does is just plain
>>>>wrong.
>>>>
>>>>
>>>>
>>>>
>>>Delicious second opinion!
>>>
>>>Thanks for the reply - if you see my second post you see I fixed my
>>>problem.
>>>
>>>Cheers,
>>>Dan.
>>>
>>>
>>>
>>>
>>>
>>>
>
>
>
>
>
Attachment: [text/html]
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.9.6 - Release Date: 4/11/2005
