Question: I have 2 tables: users and videos

* videos: videoID, userID, timestamp
* users : userID, timestamp

when I want to list all the videos that is created today, I want to limit user entries to 1. So that one user's video is returned from the query maximum. I don't want multiple videos to be returned from the same users. I will use this response as JSON format.

How can I write an sql for this purpose?

Thank you!

Answer: 
Join the two tables on the common `userID`, and then use `ROW_NUMBER` with a partition on the user to find the latest video for each user.

```
SELECT videoID, userID, timestamp
FROM
(
    SELECT u.userID, v.videoID, v.timestamp,
        ROW_NUMBER() OVER (PARTITION BY u.userID ORDER BY v.timestamp DESC) rn
    FROM users u
    INNER JOIN videos v
        ON u.userID = v.userId
) t
WHERE t.rn = 1;

```
Postgres offers `distinct on`, which is the simplest method for this:

```
select distinct on (v.userid) v.*
from videos v
where timestamp >= current_date
order by v.userid;

```

Note: You do not need the users table, because you are not requesting any columns from this. Also, this assumes that there are no future date/times in the data.