Jan has a good article about finding the row matching some value in the group:

This is one illustration of group by limitations in SQL language which is not offset by any MySQL specific extensions,yet
As you can see if you want to get one row from the group which is sorted some way you have to use ugly hacks. This is because SQL does not have a notion of sorting the data within the groups and in fact ANSI SQL even forbids you to select columns which are not aggregates or part of group by because result in this case is not defined.

What would be quite helpful is to have GROUPORDER (pick the name) clause which defines which element is selected for non aggregate columns, something like

PLAIN TEXT SQL:

1. SELECT MAX(Population),  City, Country  FROM City GROUP BY Country GROUPORDER …

The 58th edition of Log Buffer, the weekly review of database blogs, has been published by Jay Pipes (a.k.a J.Pipes) of MySQL AB on Design, Develop, Discover, Define. Chen Shapira is standing by for the next edition. Publishing a Log Buffer on your own blog is a great way to introduce yourself and your blog [...]

Dave Edwards once again has given me the privilege of writing this week's Log Buffer, and oooooh it's a jam-packed one. I think there's something for everyone in this week's issue.

Buzz in the MySQL Blogosphere...

The MySQL blogosphere was alight this past week — and not just with technical articles. The recent announcement by Kaj Arnö that MySQL was making some adjustments to its Enterprise and Community Server release schedules and removing source tarballs for the Enterprise Server from its public FTP site made waves in a number of circles. On PlanetMySQL, bloggers such as Jonathan Cheyer, Jeremy Cole, Kevin …

Bug: 30485

[miguel@skybr 4.1]$ bin/mysql -uroot db77
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.24-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `GiftCodes` (
    ->   `code` varchar(32) collate utf8_bin NOT NULL default '',
    ->   `used_by_id` bigint(10) unsigned NOT NULL default '0',
    ->   PRIMARY KEY  (`code`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> insert into GiftCodes values ('foo',7);
Query OK, 1 row affected (0.00 sec)

mysql> select * from GiftCodes where code='foo';
+------+------------+
| code | used_by_id |
+------+------------+
| foo  |          7 …

Sourcefire has bought the ClamAV project for an extraordinary, one-time charge in the third quarter of 2007 of between 9¢ and 12¢ per Sourcefire share. ClamAV is an open source project that has created an anti virus engine, packages that support its use on Linux, Unix, BSD and Win32, and a signature database that currently contains more than 147,000 signatures. It is by most estimates the most commonly used open source anti virus product in the world.

The deal covers the intellectual property, copyrights, logo, domain names, Sourceforge and Freshmeat areas, naming rights and other rights to Clam, the Open source anti virus engine and signature database. The ClamAV team - in particular Tomasz Kojm, will become Sourcefire employees and continue their management of the project on a day-to-day basis.

Do you happen to have functions that read all rows of a database result into one array, without processing the rows, and pass the array to other functions? For example, do you fetch results in your database class and pass them to a template engine displaying? If so, here is excellent news for you. mysqli_fetch_all(), which comes with mysqlnd, does the task of fetching the data sometimes twice as fast as mysqli_fetch_array(). Reason being: it saves a loop with function calls…

mixed mysqli_fetch_all( mysqli_result $result [, int $resulttype] )

The new API call, which is only available if you build ext/mysqli with mysqlnd support, does fetch all rows of a result set into an array with just one function call:

$all_rows = mysqli_fetch_all($res);

The one mysqli_fetch_all() call does exactly the same as the following loop:

Do you happen to have functions that read all rows of a database result into one array, without processing the rows, and pass the array to other functions? For example, do you fetch results in your database class and pass them to a template engine displaying? If so, here is excellent news for you. mysqli_fetch_all(), which comes with mysqlnd, does the task of fetching the data sometimes twice as fast as mysqli_fetch_array(). Reason being: it saves a loop with function calls…

mixed mysqli_fetch_all( mysqli_result $result [, int $resulttype] )

The new API call, which is only available if you build ext/mysqli with mysqlnd support, does fetch all rows of a result set into an array with just one function call:

$all_rows = mysqli_fetch_all($res);

The one mysqli_fetch_all() call does exactly the same as the following loop:

Feature:

Things to Avoid in Queries
Subqueries and Correlated subqueries
http://dev.mysql.com/doc/refman/4.1/en/correlated-subqueries.html

Jan Kneschke?s post on Groupwise Maximum:
http://jan.kneschke.de/projects/mysql/groupwise-max

Calculated comparisons do not use indexes

INSERT IGNORE
REPLACE
INSERT?ON DUPLICATE KEY

Feedback:

Email podcasttechnocation.org">podcasttechnocation.org

call the comment line at +1 617-674-2369

use Odeo to leave a voice mail through your computer:
…

mysql podcast

Feature:

Things to Avoid in Queries
Subqueries and Correlated subqueries
http://dev.mysql.com/doc/refman/4.1/en/correlated-subqueries.html

Jan Kneschke’s post on Groupwise Maximum:
http://jan.kneschke.de/projects/mysql/groupwise-max

Calculated comparisons do not use indexes

INSERT IGNORE
REPLACE
INSERT…ON DUPLICATE KEY

Feedback:

Email podcast@technocation.org

call the comment line at +1 617-674-2369

use Odeo to leave a voice mail through your …

Hi,

for the whole week I have been in training in Cupertino.  Basically, what is covered is HA/Cluster/Performance tuning.  Nothing very special this week,  I just continue to discover MySQL from the inside and I’m pleased with what I see.  Tonight, we have a dinner with our VP, Ulf Sandberg.  Since such a large proportion of MySQL employees work remotely,  such events are important make ties between people.

I started looking at the MySQL proxy and although some may argue that features it allows should be in the server, it is so easy to do interesting things.   Of course, I will have to learn LUA and… of course Jan had chosen to use LUA 5.1 and my brand new Ubuntu Feisty Laptop has LUA 5.0.  Anyway, I don’t really need to compile it right now, there is a binary version available.

I almost done with the configuration of my Lenovo T61, yesterday I got my bluetooth …