An example of the data could look like:

| username | tweet | is\_public | created\_at |
| --- | --- | --- | --- |
| catelie | tweet1 | 1 | 2021-06-14 |
| urs | tweet1 | 1 | 2021-06-15 |
| nik | firstlast | 1 | 2021-06-15 |
| urs | lasttweet | 1 | 2021-06-23 |
| nik | empty | 0 | 2021-06-23 |

To select all ***public tweets***:

```
 WHERE is_public = 1 

```

To select ***newest tweet per user*** I was inspired by [these](https://stackoverflow.com/questions/612231/how-can-i-select-rows-with-maxcolumn-value-partition-by-another-column-in-mys/27802817#27802817) so I got:

```
SELECT posts.* FROM post
  LEFT JOIN posts as postdouble
    ON posts.created_at < postdouble.created_at AND posts.username = postdouble.username
WHERE postdouble.username IS NULL

```

To select ***newest and public tweet per user*** (mix up these two statements) I'm lost by some SubSubQueries

```
SELECT posts.username, posts.tweet, posts.is_public, posts.created_at, postdouble.username as userd, postdouble.tweet as tweetd, postdouble.is_public as ispublicd, postdouble.created_at as createdatd FROM posts
  LEFT JOIN posts as postdouble
    ON posts.created_at < (
         SELECT MAX(posts.created_at) FROM posts WHERE is_public = 1
      )
    AND posts.username = postdouble.username
-- WHERE postdouble.username IS NULL

```

urs is selected right (empty entities in doubles) but that's the only thing thats right. Please help me with some debugging.thx^^lg