Im wondering if someone could help me.
I have hundreds of people uploading images to my site, basically each upload has its own row in the database with the data created, user id, images name etc etc etc.
What i want to do is display the last 3 users to upload images the problem being that if a user uploads more pics, another row is added to the database with that users id so i can just show the last 3 rows.
I have the following statement, but it doesnt seem to be working properly
SELECT DISTINCT * FROM kicks GROUP BY uid ORDER BY created DESC LIMIT 3
I was wondering if someone could point me out where im going wrong.
Cheers
Sorry, i should have added sample data, ok
id | uid | created |
195 | 33 | 2012-03-06 12:32:54
196 | 33 | 2012-03-06 12:35:23
197 | 34 | 2012-03-06 13:09:31
198 | 19 | 2012-03-08 10:37:21
199 | 33 | 2012-03-09 21:04:04
SELECT DISTINCT uid FROM kicks ORDER BY created DESC LIMIT 3 is what you want.
Try this:
SELECT * FROM kicks WHERE uid IN (
SELECT DISTINCT uid FROM kicks ORDER BY created DESC
) LIMIT 3
or if your id column is auto_incremented, try to order by it:
SELECT * FROM kicks WHERE uid IN (
SELECT DISTINCT uid FROM kicks ORDER BY id DESC
) LIMIT 3
Databases have a unique ID field, so a new row will have a new unique ID, meaning DISTINCT is redundant as every field is distinct if you're using SELECT *.
Why not try something like
SELECT DISTINCT uid FROM kicks
ORDER BY created
DESC LIMIT 3
Like I say, using * means every field is distinct.
e: You may need to provide more information on your database structure, that will help to understand how the fields need to be selected.
There's really no problem with your query. The only problem is that when you group by users, you need to output the users personal greatest upload time with a function like MAX(). I got it working with similar data to yours.
Try something like lines of:
SELECT uid, MAX(created) as latest FROM kicks GROUP BY uid ORDER BY created DESC LIMIT 3
This will add the column "latest" to your query result: )
My version will allow you do display the rows unique ID, if you need that for some reason, but I think the accepted DISTINCT version is cleaner if not needed.