Help needed - mysql query

[nareshrevoori]

Guys, i am new to this and i really appreciate your help.

I have two tables 1) status 2) friends



please see the above DB image and heres my query

SELECT message,time FROM status WHERE uid in (SELECT uid2 FROM friend WHERE uid1=10)

above query returns all message of each friend, i just want only one most recent message. How can i modify this query to get it done??

and please i want it without using aliasing

Thanks a lot

[Dr John]

Your table is not normalised, and will always cause you problems.

Friends should be {uid1, uid2}, and each field should contain just ONE id number. but they should also form a joint key for the table, so that 10,12 is the key to one row, and 10,53 is the key to the next row and 10,9 is the joint key to the third row.

The data returned by your sub query is the single STRING 12,53,9 and you are comparing the INT uid with this string. (I assume it's an int).

[nareshrevoori]

i gave those table as for sample, they are not real

Another view:

SELECT message,time FROM status WHERE uid in (12,53,9);

with that query, it returns 2 rows for each user, i need latest/most recent one

SELECT message,time FROM status WHERE uid in (12,53,9) LIMIT 1;

if i try something like above it gives me only one row

please, show me how can i modify it so that it will always returns most recent one message for each user.