SQL Select newest (public) post of each user

Question

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 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

. . I'm a little confused on what your queries have to do with the question you have asked. — Gordon Linoff, Jul 06 '21 at 22:27
My query contains the "newest tweet per user" (from the link I posted and that contains the "p1.created_at — DarioDS, Jul 06 '21 at 23:50

Gordon Linoff · Accepted Answer · 2021-07-07T00:10:53.440

0

You are overcomplicating this. You can use a correlated subquery, but you don't need an additional JOIN:

SELECT p.*
FROM posts p
WHERE p.created_at = (SELECT MAX(p2.created_at)
                      FROM posts p2
                      WHERE p2.is_public = 1 AND
                            p2.username = p.username
                     ) AND
      p.is_public = 1;

The correlation clause makes sure it is for the same user.

For optimal performance, you want an index on posts(username, public, created_at).

edited Jul 07 '21 at 00:10

answered Jul 06 '21 at 21:28

Gordon Linoff

1,242,037
58
646
786

1

thx, me start loving stackoverflow :) I'll mark you answer as cvorrect as soon I'm allowed to mark it (I'm too new here to mark or upvote anythig now) – DarioDS Jul 06 '21 at 21:35
With your answer I "corrected" my Code: 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.username = postdouble.username AND posts.created_at < ( SELECT MAX(postdouble.created_at) FROM posts WHERE postdouble.is_public = 1 ) WHERE postdouble.username IS NULL AND posts.is_public = 1 BUT, yeah, all criteria (is_public = 1 for example) needs to listed twice^^lg – DarioDS Jul 06 '21 at 21:53
@DarioDS . . . This answer does not have a `JOIN`. – Gordon Linoff Jul 06 '21 at 22:27
yeah, and you answer also contains no GROUP BY(). (In compare of the solution that was marked as "correct" linked above ). I just posted my "working-code" in full compare of your shorter answer (seems both run in +-10% same speed, and for sure I addopted your shortening to my production)! thx again (and I now was able to mark your answer as the correct one)^^lg – DarioDS Jul 06 '21 at 23:53
I think they run at same speed as all criteria (is_public = 1 for example) needs to listed twice (in my code and also in your shortening). Or are there a solution (without "window function" nor the "top 1 with ties") where you only have to list "is_public = 1" once? – DarioDS Jul 06 '21 at 23:57

score 0 · Answer 2 · answered Jul 06 '21 at 21:38

One other easy way would be to use a window function to pick off the required rows

select * from (
    select *, Row_Number() over(partition by username order by created_at desc) rn
    from posts
    where is_public=1
)p
where rn=1

You could also in this instance get the same result using top 1 with ties

select top (1)  with ties *, Row_Number() over(partition by username order by created_at desc) rn
from posts
where is_public=1
order by rn

SQL Select newest (public) post of each user

2 Answers2