Hello

I have this table:
images
id (int 19), user_id (int 19), creation_date (date), image (varchar
10)

insert into images values(1, 1, '2010-01-01', 'img1');
insert into images values(2, 1, '2010-01-02', 'img2');
insert into images values(3, 2, '2010-01-01', 'img3');

I want to get rows where the user only shows up once.
If I run:
select * from images group by user_id
then I get rows with the id:s 1 and 3.

The thing is that for a specific user_id I need to get the row that
was inserted most recently.
The user_id 1 has been inserted twice. First time 2010-01-01, second
time 2010-01-02.
I need to retrive the row with the date 2010-01-02.
I tried ordering by creation-date but this didnt help. It must be a
clause I think. But I must confess I dont know wich one. Any advice?

On Dec 6, 10:26*am, pac Man <querystra... (AT) gmail (DOT) com> wrote:
As usual, you need the strawberry query.
http://dev.mysql.com/doc/refman/5.0/...group-row.html

Assuming that the id column is an auto increment one (so the most
recent date will also be the highest id), the query is:
SELECT
i1.id,
i1.user_id,
i1.creation_date,
i1.image
FROM images i1
LEFT JOIN images i2 ON i1.user_id = i2.user_id AND i1.id < i2.id
WHERE i2.id IS NULL;

If the assumption about the dates and ids is not true, then use
creation_date instead of id.