On Thursday 16 August 2001 10:52, Fabian Groene wrote:
(snip)
> My problem is quite simple indeed and only refers to the SQL query:
>
>
> There is a table with quite a lot of columns. But only two of them are
> really important for my query: One field is a date field and the other one
> is a group name. My aim is to create a query that only selects the item
> with the most recent date. That is simple and can be handled by max(date).
> But only the most recent item from each group shall be given as a result of
> my query. That's also fine.
If I understand you correctly you have a table somewhat like this:
+---------+------------+-------+
| item | gdate | gname |
+---------+------------+-------+
| this | 2001-10-01 | ABC |
| that | 2001-01-01 | ABC |
| the | 2000-09-01 | Hello |
| other | 2000-06-01 | Hello |
| no idea | 2000-10-10 | Hello |
+---------+------------+-------+
(but probably more complex). For each group with in the column 'gname' you
want to extract the item (or what other data you have) with the most recent
date. So your desired result would be:
+---------+------------+-------+
| item | gdate | gname |
+---------+------------+-------+
| this | 2001-10-01 | ABC |
| no idea | 2000-10-10 | Hello |
+---------+------------+-------+
(Or am I missing something?)
> With the query
>
> select groupname,max(date) from databasename Group By groupname
>
> I got what I wanted and was happy.
For the above example the query and result set looks like this:
mysql> select gname, max(gdate) from groups group by gname;
+-------+------------+
| gname | max(gdate) |
+-------+------------+
| ABC | 2001-10-01 |
| Hello | 2000-10-10 |
+-------+------------+
Which, I agree, _looks_ right.
> BUT: As soon as I told the database to
> read out more fields it no longer worked to get the most recent item from
> the particular groups. Instead there were many items having the same group
> which I wanted to supress with max(date)
>
> My query was in the form of "select groupname,field1,fieldn,max(date) from
> databasename Group By groupname, field1,fieldn ...."
So you ended up with something like this?
mysql> select gname, max(gdate), item, gdate from groups group by gname,
item, gdate;
+-------+------------+---------+------------+
| gname | max(gdate) | item | gdate |
+-------+------------+---------+------------+
| ABC | 2001-01-01 | that | 2001-01-01 |
| ABC | 2001-10-01 | this | 2001-10-01 |
| Hello | 2000-10-10 | no idea | 2000-10-10 |
| Hello | 2000-06-01 | other | 2000-06-01 |
| Hello | 2000-09-01 | the | 2000-09-01 |
+-------+------------+---------+------------+
> Has anyone an idea how to overcome this problem?
Yes and no. An obvious (but misleading solution) would be this:
mysql> select gname, max(gdate), item, gdate from groups group by gname;
+-------+------------+------+------------+
| gname | max(gdate) | item | gdate |
+-------+------------+------+------------+
| ABC | 2001-10-01 | this | 2001-10-01 |
| Hello | 2000-10-10 | the | 2000-09-01 |
+-------+------------+------+------------+
(not sure whether that's valid SQL; applications like Oracle or PostgreSQL
certainly would't accept the statement as it is).
Looks almost right, but for the group "Hello" we have completely the wrong
item and date :-(
Unfortunately I don't think what you want to do can be done in MySQL. It's
something I've run into once or twice and haven't been able to resolve. Thus
far I've coded round it on the application side. Ideas anyone?
The usual solution would be a subselect a la:
select gname, item, gdate
from groups
where gdate in (select max(gdate)
from groups
group by gname)
Alas no subselects yet in MySQL (see:
http://www.mysql.com/doc/M/i/Missing_Sub-selects.html )
HTH in some way anyway
Ian Barwick
--
Ian Barwick - Developer - [EMAIL PROTECTED]
akademie.de asp GmbH - http://www.akademie.de
"To query tables in a MySQL database is more fun than eating spam"
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
