SELECT DISTINCT USER ID by unix timestamp

[rocoso]

Hi

 

Im trying to select distinct user_id by unix timestamp.

Im storing unix timestamps in the DB ex: 1264401846

 

I just want the user_id's of the most recent uploaded photos

 

This is what i have so far but it returns duplicates

$datetime = mktime();

 

$sql="SELECT DISTINCT(user_id), upload_date FROM image_albums WHERE FROM_UNIXTIME(upload_date) < FROM_UNIXTIME(".$datetime.") ORDER BY upload_date DESC LIMIT 3";

 

HELP??

 

[PFMaBiSmAd]

DISTINCT is not a function. It does not apply to a single column you select. It applies to all the columns you select. It simply removes duplicate rows from the result set.

 

What exactly are you trying to do, please show an example of some data and the expected results.

 

 

[rocoso]

Im trying to get the user_id's  of members according the the latest uploaded photo ordered by the unix timestamp

 

ex:

user_id | upload_date

34          1264401831

80          1264401822

21          1264401813

[PFMaBiSmAd]

Actually, that's not much of an example to show what you are trying to do. However, I'll guess that you want the latest row for each user. If so, see this link - http://dev.mysql.com/doc/refman/5.1/en/example-maximum-column-group-row.html

[rocoso]

Thanks PFMaBiSmAd!! 

 

This is working

SELECT s1.user_id,FROM_UNIXTIME(s1.upload_date) FROM image_albums s1
LEFT JOIN image_albums s2 ON s1.user_id = s2.user_id AND FROM_UNIXTIME(s1.upload_date) < FROM_UNIXTIME(s2.upload_date)
WHERE s2.user_id IS NULL ORDER by FROM_UNIXTIME(s1.upload_date) DESC LIMIT 8