Help with a specific MySQL query: how to get the smallest value from a group of several values.

Question

I have a table 'foo' with a timestamp field 'bar'. How do I get only the oldest timestamp for a query like: SELECT foo.bar from foo? I tried doing something like: SELECT MIN(foo.bar) from foo but it failed with this error

ERROR 1140 (42000) at line 1: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

OK, so my query is much more complicated than that and that's why I am having a hard time with it. This is the query with the MIN(a.timestamp):

select distinct a.user_id as 'User ID',
       a.project_id as 'Remix Project Id',
       prjs.based_on_pid as 'Original Project ID',
       (case when f.reasons is NULL then 'N' else 'Y' end)
         as 'Flagged Y or N',
       f.reasons, f.timestamp, MIN(a.timestamp) 
from view_stats a
     join (select id, based_on_pid, user_id
                    from projects p) prjs on
     (a.project_id = prjs.id)
     left outer join flaggers f on
     (    f.project_id = a.project_id
      and f.user_id = a.user_id)
where a.project_id in
(select distinct b.id
   from projects b
  where b.based_on_pid in
                ( select distinct c.id
                    from projects c
                   where c.user_id = a.user_id
                )
)
order by f.reasons desc, a.user_id, a.project_id;

Any help would be greatly appreciated.

The view_stats table:

+------------+------------------+------+-----+-------------------+----------------+
| Field      | Type             | Null | Key | Default           | Extra          |
+------------+------------------+------+-----+-------------------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL              | auto_increment | 
| user_id    | int(10) unsigned | NO   | MUL | 0                 |                | 
| project_id | int(10) unsigned | NO   | MUL | 0                 |                | 
| ipaddress  | bigint(20)       | YES  | MUL | NULL              |                | 
| timestamp  | timestamp        | NO   |     | CURRENT_TIMESTAMP |                | 
+------------+------------------+------+-----+-------------------+----------------+

Update

Based on thomas' suggestion I converted my query to:
select distinct a.user_id as 'User ID',
       a.project_id as 'Remix Project Id',
       prjs.based_on_pid as 'Original Project ID',
       (case when f.reasons is NULL then 'N' else 'Y' end)
         as 'Flagged Y or N',
       f.reasons, f.timestamp, min(a.timestamp)
from view_stats a
     join (select id, based_on_pid, user_id
                    from projects p) prjs on
     (a.project_id = prjs.id)
     left outer join flaggers f on
     (    f.project_id = a.project_id
      and f.user_id = a.user_id)
where a.project_id in
(select distinct b.id
   from projects b
  where b.based_on_pid in
                ( select distinct c.id
                    from projects c
                   where c.user_id = a.user_id
                )
)
group by a.project_id, a.user_id
order by a.timestamp
;

It's running now.

Answer 1

If you are going to use aggregate functions (like min(), max(), avg(), etc.) you need to tell the database what exactly it needs to take the min() of.

transaction    date
one            8/4/09
one            8/5/09
one            8/6/09
two            8/1/09
two            8/3/09
three          8/4/09

I assume you want the following.

transaction    date
one            8/4/09
two            8/1/09
three          8/4/09

Then to get that you can use the following query...note the group by clause which tells the database how to group the data and get the min() of something.

select
    transaction,
    min(date)
from
    table
group by
    transaction