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

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