Mysql query problem
 

Hello my fellow programmers,

I'm not one of the good ones at this so I could really need som help.

I have a table that I use as a log for a system.

In this log I have the columns: id, fname, lname, number, status and date.
Date is auto generated with date and time.

The column "id" is not unique.

The table is full of data and some id's have multiple status.
Short simplified example below:
id status date
5 return 20110101
5 return 20110102
5 no answer 20110103
5 return 20110104
5 no answer 20110105
6 return 20110101
6 return 20110102
6 no answer 20110103
6 return 20110104
6 no answer 20110105
6 return 20110106

Now I need to search the entire table for every "id" where the latest date for each "id" resulted in a "no answer".

From my short table example above I must get the results:
id status date
5 no answer 20110105
6 no answer 20110105

Let's start with the basic SQL.

Select id from your table

Use "GROUP BY id" and "MAX(date)".

Thanks to eSelix

That solved the problem.

Hey eSelix, usually when learning SQL, I tend not to give answers. It defeats the purpose of learning.

Yes, I understand and promote that, not only with SQL. I refrained before giving whole query, this was only a hint, as I stated that your answer "SELECT id" was too general.

Ah, good job. I thought he was going to use a where clause, but group by and max worked too. There is more then one way to write the sql query he wants. The smaller the query, the better.