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: 
You may want to consider the `max` windowing function:

```
with videoz as (
  select
    v.*,
    max (v.timestamp) over (partition by v.user_id) as max_timestamp
  from
    videos v
    join users u on v.userid = u.user_id
)
select
  videoid, userid, timestamp
from videoz
were
  timestamp = max_timestamp

```

In theory, this should be *O(n)*, which is about as good as you can expect. The one drawback over using `row_number` is that if you have two videos for the same user with the same timestamp, this will return both rows, in which case Tim's solution would be preferred, although if there is a tie you should probably have some methodology of choosing one or the other.
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.