Re: Display 1st row of every group?

Mon, 11 Apr 2005 09:31:10 -0700

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" <[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.
   

        
Delicious second opinion!

Thanks for the reply - if you see my second post you see I fixed my
problem.

Cheers,
Dan.


 

      



  
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


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Reply via email to