List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:April 11 2005 2:56pm
Subject:Re: Display 1st row of every group?
View as plain text  
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.

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