I'm trying to retrieve information from one database and use it to order the details of another.
First I'll give a basic interpretation of my database situation. (Bold words are table names)

watch -
user_id | watch_id

events -
user_id | time


What I'm attempting to do, is get the watch_id's from watch and find its first user_id equivalent in events and then subsequently order the results by time desc.

For example:
watch -
1 | 34
1 | 56

events -
56 | 1235278075
75 | 1235235986
34 | 1235280000
34 | 1000050000

So assuming my User_id is 1, I need to get the user_id's in events that are in my watch_id's in watch and then have it so that in a query, they're ordered by time DESC.
In this case, it would give me the results:
34 | 1235280000
56 | 1235278075

I hope I've explained the situation properly. Any help would be greatly appreciated. Thanks

Unless I'm misunderstanding it should be:

SELECT watch.watch_id 
FROM watch, events 
WHERE watch.user_id = events.user_id 
ORDER BY events.time DESC;

Hey, its a mix of misunderstanding and bad explanation so I'll have another go.


Say my user_id in watch is 1. Get my watch_id's in watch.
Then, get the first instance where watch_id (in watch) = user_id (in events) ordered by time DESC.


The query you suggested for some reason doesn't put them in order of time. I also need the user_id's in event to be distinct so theres only one instance of them in the results.

The best I've got is with this query:
SELECT DISTINCT watch_id FROM watch w JOIN events e ON e.user_id = w.user_id WHERE w.user_id = 1 ORDER BY e.time DESC

However, even though it does give me the distinct user_id rows in events based on the watch_id's in watch. However, they're STILL not ordered by time.
If I turn it into this:
SELECT * FROM watch w JOIN events e ON e.user_id = w.user_id WHERE w.user_id = 1 ORDER BY e.time DESC
It is in order of time, but its not distinct!

I hope this clarifies things slightly.

Hey, I managed to solve the issue:

SELECT e.user_id, MAX(e.time) FROM watch w JOIN events e ON e.user_id = w.watch_id WHERE w.user_id = 1 GROUP BY e.user_id ORDER BY MAX(e.time) DESC LIMIT 25