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

> 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/mysql?unsub=1
>
>
> -- 
> 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

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