----- Original Message ----- From: "Dan Bolser" <[EMAIL PROTECTED]> To: <mysql@lists.mysql.com> 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. > 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?) > No, absolutely not. Try it yourself with a small table of sample data and you will see for yourself. Your biggest problem is that you are trying to select all the columns of the table but only grouping on one column. The whole intent of GROUP BY is to summarize data, not give lots of details, but you are asking for details. There is probably a way to get what you want but it would take me a fair while to properly explain GROUP BY to you and I can't spare that much time. As a temporary expedient, may I suggest that you: - change the SELECT * clause so that it names only the column(s) in the GROUP BY, plus any summarizations (SUM(), COUNT(*), AVG(), etc.) you need - drop the HAVING altogether - use ORDER BY, if necessary, to make sure that your result set rows come out in the desired order Also, be sure to identify which version of MySQL you are using when you post an SQL question. In many, many cases the answer to an SQL question is "It depends on which version of MySQL you are using." The later versions, 4.1 and 5.0, have many more options that could be useful for this problem than the older versions. Rhino > Cheers, > Dan. > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > > -- > No virus found in this incoming message. > Checked by AVG Anti-Virus. > Version: 7.0.308 / Virus Database: 266.9.5 - Release Date: 07/04/2005 > > -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.6 - Release Date: 11/04/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
