List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:April 11 2005 4:30pm
Subject:Re: Display 1st row of every group?
View as plain text  
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
Thread
Display 1st row of every group?Dan Bolser11 Apr
  • Re: Display 1st row of every group?Dan Bolser11 Apr
    • Re: Display 1st row of every group?Peter Brawley11 Apr
      • Re: Display 1st row of every group?Dan Bolser11 Apr
  • Re: Display 1st row of every group?Rhino11 Apr
    • Re: Display 1st row of every group?Dan Bolser11 Apr
      • Re: Display 1st row of every group?Peter Brawley11 Apr
        • Re: Display 1st row of every group?Dan Bolser11 Apr
          • Re: Display 1st row of every group?Peter Brawley11 Apr
            • Analyze TableEmmett Bishop11 Apr