mysql first record retrieval

Question

While very easy to do in Perl or PHP, I cannot figure how to use mysql only to extract the first unique occurence of a record.

For example, given the following table:

Name   Date         Time          Sale
John   2010-09-12   10:22:22      500
Bill   2010-08-12   09:22:37      2000
John   2010-09-13   10:22:22      500
Sue    2010-09-01   09:07:21      1000
Bill   2010-07-25   11:23:23      2000
Sue    2010-06-24   13:23:45      1000

I would like to extract the first record for each individual in asc time order. After sorting the table is ascending time order, I need to extract the first unique record by name.

So the output would be :

Name   Date         Time          Sale
John   2010-09-12   10:22:22      500
Bill   2010-07-25   11:23:23      2000
Sue    2010-06-24   13:23:45      1000

Is this doable in an easy fashion with mySQL?

Thanks, Sammy

Answer 1

I think that something along the lines of

select name, date, time, sale from mytable order by date, time group by name;

will get you what you're looking for

Answer 2

In databases, there really is no "first" or "last" record; think of each record as its own, non-positional entity in the table. The only positions they have are when you give them one, say, using ORDER BY.

This will give you what you want. It might not be efficient, but it works.

select Name, Date, Time, Sale from
    (select Name, Date, Time, Sale from MyTable
        order by Date asc, Time asc) MyTable_subquery_name
group by Name

Note: MyTable_subquery_name is just a dummy name for the subquery. MySQL will give the error ERROR 1248 (42000): Every derived table must have its own alias without it.

If only GROUP BY and ORDER BY were communicative operations, then this wouldn't have to be a subquery.

Answer 3

you need to perform a groupwise max or groupwise min

see below or http://pastie.org/973117 for an example

 select
  u.user_id,
  u.username,
  latest.comment_id
 from
  users u
 left outer join
 (
  select
   max(comment_id) as comment_id,
   user_id
 from
  user_comment
 group by
  user_id
 ) latest on u.user_id = latest.user_id;