From: Peter Brawley Date: April 11 2005 2:10pm Subject: Re: Display 1st row of every group? List-Archive: http://lists.mysql.com/mysql/182436 Message-Id: <425A8568.6020606@earthlink.net> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="=======AVGMAIL-425A856814E2=======" --=======AVGMAIL-425A856814E2======= Content-Type: multipart/alternative; boundary=------------070601030901050500080507 --------------070601030901050500080507 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit />I guess that entry is either wrong or misleading./ Ordering by another column which isn't mutually dependent with the grouping column will have unpredictable results. Is that what you mean by the example being "wrong or misleading"? PB ----- Dan Bolser wrote: >I guess that entry is either wrong or misleading. > >I can get what I want like this > > SELECT * > FROM tbl >INNER JOIN ( SELECT id, min(bleah) as bleah > FROM tbl > GROUP BY id > ) > USING (id,bleah); > >Which will work so long as bleah has a unique minimum value per id group. > >On Mon, 11 Apr 2005, Dan Bolser wrote: > > > >>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; >> >>I want to use this syntax with an 'order by' like this... >> >> SELECT * >> FROM tbl >>GROUP BY id >> HAVING count(*) = 1 >>ORDER BY bleah; >> >>Will this syntax return the row within the "id group" with the smallest >>value of the bleah column? (is it guaranteed to do so?) >> >>Cheers, >>Dan. >> >> >> >> >> > > > > --------------070601030901050500080507 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit >I guess that entry is either wrong or misleading.

Ordering by another column which isn't mutually dependent with the grouping column will have unpredictable results. Is that what you mean by the example being "wrong or misleading"?

PB

-----

Dan Bolser wrote: 
I guess that entry is either wrong or misleading.

I can get what I want like this

    SELECT * 
      FROM tbl 
INNER JOIN (	  SELECT id, min(bleah) as bleah
		    FROM tbl
		GROUP BY id
		)
    USING (id,bleah);

Which will work so long as bleah has a unique minimum value per id group.

On Mon, 11 Apr 2005, Dan Bolser wrote:

  
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;

I want to use this syntax with an 'order by' like this...

 SELECT *
   FROM tbl
GROUP BY id
 HAVING count(*) = 1
ORDER BY bleah;

Will this syntax return the row within the "id group" with the smallest
value of the bleah column? (is it guaranteed to do so?)

Cheers,
Dan.
--------------070601030901050500080507-- --=======AVGMAIL-425A856814E2======= Content-Type: text/plain; x-avg=cert; charset=us-ascii Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Content-Description: "AVG certification" No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.5 - Release Date: 4/7/2005 --=======AVGMAIL-425A856814E2=======--