Select first and last row based on each user id mysql

Question

I want to select every first and last event of every user id, if no first event exists,then just the last event instead . tried using partition over , but i am getting first 2 events instead.

Input:

id  timestamp   event
10096   1545136190  songs
10096   1545136202  tv
10096   1545136239  book
10096   1545136244  eat
10096   1545136252  travel
10096   1545136271  tv
10096   1545136275  laptop
10096   1545313396  sleep
10105   1545464444  book
10105   1545464455  eat
10105   1545464457  travel
10105   1545464460  tv
10105   1545464605  book
10105   1546740388  sleep
10213   1545134176  tv
10213   1545134180  computer
10213   1545134221  play
10213   1545134238  book
10213   1545134267  eat
10213   1545146190  travel
10213   1545146212  play
10213   1545623861  sleep
1053    1545131279  sleep

Expected Output:

   id   timestamp   event
10096   1545136190  songs
10096   1545313396  sleep
10105   1545464444  book
10105   1546740388  sleep
10213   1545134176  tv
10213   1545623861  sleep
1053    1545131279  sleep

tried doing this but not getting exactly what i want:

SELECT * FROM (
SELECT 
    ROW_NUMBER() OVER(PARTITION BY id ORDER BY id) as RowNum, 
    id, 
    timestamp,
    event
FROM
    `table`
) MyData
WHERE RowNum < 3

Answer 1

Here's one way:

SELECT id, timestamp, event 
FROM (
    SELECT id, timestamp, event  
         , ROW_NUMBER() OVER(PARTITION BY id ORDER BY timestamp asc) as r1
         , ROW_NUMBER() OVER(PARTITION BY id ORDER BY timestamp desc) as r2
    FROM table
) MyData
WHERE 1 IN (r1,r2)

The idea is to order each id by timestamp in both ascending and descending order, then pick the first one for each ordering.

Answer 2

SELECT id, 
       MIN(`timestamp`) first_timestamp, 
       FIRST_VALUE(event) OVER (PARTITION BY id ORDER BY timestamp ASC) first_event,
       MAX(`timestamp`) last_timestamp, 
       LAST_VALUE(event) OVER (PARTITION BY id ORDER BY timestamp ASC) last_event
 -- or FIRST_VALUE(event) OVER (PARTITION BY id ORDER BY timestamp DESC) last_event
FROM `table`
GROUP BY id