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.
>>
>>
>>
>>
>
