By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE post (id int NOT NULL AUTO_INCREMENT
, postDate datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, userid int NOT NULL, threadId int NOT NULL, PRIMARY KEY(id));
INSERT INTO post (postDate, userid, threadId) VALUES("2025-3-1 16:30:00", 1, 7);
INSERT INTO post (postDate, userid, threadId) VALUES("2025-3-1 17:57:00", 2, 7);
INSERT INTO post (postDate, userid, threadId) VALUES("2025-3-1 19:23:00", 3, 7);
CREATE TABLE users (id int NOT NULL AUTO_INCREMENT, name varchar(50), PRIMARY KEY(id));
INSERT INTO users (id, name) VALUES(1, "Bob");
INSERT INTO users (id, name) VALUES(2, "Steve");
INSERT INTO users (id, name) VALUES(3, "Mary");
CREATE TABLE thread (id int NOT NULL AUTO_INCREMENT, title varchar(100), PRIMARY KEY(id));
INSERT INTO thread (id, title) VALUES(5, "Another thread");
INSERT INTO thread (id, title) VALUES(6, "Some foxy stuff");
INSERT INTO thread (id, title) VALUES(7, "This is a test");
select * from post;
| id | postDate | userid | threadId |
|---|---|---|---|
| 1 | 2025-03-01 16:30:00 | 1 | 7 |
| 2 | 2025-03-01 17:57:00 | 2 | 7 |
| 3 | 2025-03-01 19:23:00 | 3 | 7 |
with first_last as(
select threadId,cnt
,min(case when rn=1 then userId end) uFirst
,min(case when rn=cnt then userId end) uLast
,min(case when rn=1 then postDate end) pFirst
,min(case when rn=cnt then postDate end) pLast
from(
select *
,row_number()over(partition by threadId order by postDate)rn
,count(*)over(partition by threadId)cnt
from post
)q
where rn=1 or rn=cnt
group by threadId
)
select t.*, uFirst,pFirst,uLast,pLast,cnt
from thread t
left join first_last p on p.threadid=t.Id
| id | title | uFirst | pFirst | uLast | pLast | cnt |
|---|---|---|---|---|---|---|
| 5 | Another thread | null | null | null | null | null |
| 6 | Some foxy stuff | null | null | null | null | null |
| 7 | This is a test | 1 | 2025-03-01 16:30:00 | 3 | 2025-03-01 19:23:00 | 3 |
with first_last as(
select *
from(
select *
,row_number()over(partition by threadId order by postDate)rn
,count(*)over(partition by threadId)cnt
from post
)q
where rn=1 or rn=cnt
)
select t.*
,p1.userId as uFirst,p1.PostDate as pFirst
,p2.userId as uLast,p2.PostDate as pLast
,p1.cnt postCount
from thread t
left join first_last p1 on p1.threadid=t.Id and p1.rn=1
left join first_last p2 on p2.threadid=t.Id and p2.rn=p2.cnt
| id | title | uFirst | pFirst | uLast | pLast | postCount |
|---|---|---|---|---|---|---|
| 5 | Another thread | null | null | null | null | null |
| 6 | Some foxy stuff | null | null | null | null | null |
| 7 | This is a test | 1 | 2025-03-01 16:30:00 | 3 | 2025-03-01 19:23:00 | 3 |
select t.*
,p1.userId as uFirst,p1.PostDate as pFirst
,p2.userId as uLast,p2.PostDate as pLast
,fl.cnt postCount
from thread t
left join (
select threadId,min(postDate)pFirst,max(postDate)pLast,count(*) cnt
from post
group by threadId
) fl on fl.threadid=t.Id
left join post p1 on p1.threadid=t.Id and p1.postDate=fl.pFirst
left join post p2 on p2.threadid=t.Id and p2.postDate=fl.pLast
| id | title | uFirst | pFirst | uLast | pLast | postCount |
|---|---|---|---|---|---|---|
| 7 | This is a test | 1 | 2025-03-01 16:30:00 | 3 | 2025-03-01 19:23:00 | 3 |
| 5 | Another thread | null | null | null | null | null |
| 6 | Some foxy stuff | null | null | null | null | null |