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(200), 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;
select * from thread;
| 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 |
| id | title |
|---|---|
| 5 | Another thread |
| 6 | Some foxy stuff |
| 7 | This is a test |
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 |
|---|---|---|---|---|---|---|
| 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 |
-- test model
-- insert 10 users
insert into users (id,name)
with recursive ur as(
select coalesce((select max(Id) from users),0)+1 userId, 'Added user first' name
union all
select r.userId+1 userId,concat('User-',r.userid+1) name
from ur r where r.userid<10
)
select userId id,name from ur;
select * from users;
Records: 7 Duplicates: 0 Warnings: 0
| id | name |
|---|---|
| 1 | Bob |
| 2 | Steve |
| 3 | Mary |
| 4 | Added user first |
| 5 | User-5 |
| 6 | User-6 |
| 7 | User-7 |
| 8 | User-8 |
| 9 | User-9 |
| 10 | User-10 |
-- insert 40 threads
insert into thread (id,title)
with recursive tr as(
select coalesce((select max(Id) from thread),0)+1 threadId
,concat('Added title',' first',space(20)) title
union all
select r.threadId+1 threadId, concat('Title for ',r.threadId+1) title
from tr r where r.threadId<40
)
select threadId id,title from tr;
select * from thread;
Records: 33 Duplicates: 0 Warnings: 0
| id | title |
|---|---|
| 5 | Another thread |
| 6 | Some foxy stuff |
| 7 | This is a test |
| 8 | Added title first |
| 9 | Title for 9 |
| 10 | Title for 10 |
| 11 | Title for 11 |
| 12 | Title for 12 |
| 13 | Title for 13 |
| 14 | Title for 14 |
| 15 | Title for 15 |
| 16 | Title for 16 |
| 17 | Title for 17 |
| 18 | Title for 18 |
| 19 | Title for 19 |
| 20 | Title for 20 |
| 21 | Title for 21 |
| 22 | Title for 22 |
| 23 | Title for 23 |
| 24 | Title for 24 |
| 25 | Title for 25 |
| 26 | Title for 26 |
| 27 | Title for 27 |
| 28 | Title for 28 |
| 29 | Title for 29 |
| 30 | Title for 30 |
| 31 | Title for 31 |
| 32 | Title for 32 |
| 33 | Title for 33 |
| 34 | Title for 34 |
| 35 | Title for 35 |
| 36 | Title for 36 |
| 37 | Title for 37 |
| 38 | Title for 38 |
| 39 | Title for 39 |
| 40 | Title for 40 |
set @@cte_max_recursion_depth=50000
-- insert 40000 posts
insert into post (postDate,userId,threadId)
with recursive pr as(
select coalesce((select max(Id) from post),0)+1 postId
,date_add('2025-01-01 00:00:01', interval (ceiling(rand()*100000)) second) postDate
, 10 userId
, 9 threadId
union all
select r.postId+1 threadId
,date_add('2025-01-01 00:00:01', interval (ceiling(rand()*10000000)) second) postDate
, ceiling(rand()*10) userId
,ceiling(rand()*40)threadId
from pr r where r.postId<40000
)
select postDate,userId,threadId from pr;
-- select * from post;
Records: 39997 Duplicates: 0 Warnings: 0
SET profiling = 1;
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 | 3 | 2025-01-01 01:04:05 | 7 | 2025-04-26 17:13:50 | 1011 |
| 6 | Some foxy stuff | 3 | 2025-01-01 05:26:31 | 5 | 2025-04-26 17:36:24 | 985 |
| 7 | This is a test | 5 | 2025-01-01 01:15:46 | 3 | 2025-04-26 11:07:43 | 1054 |
| 8 | Added title first | 7 | 2025-01-01 02:28:32 | 9 | 2025-04-26 17:11:49 | 1032 |
| 9 | Title for 9 | 3 | 2025-01-01 00:39:25 | 1 | 2025-04-26 14:22:45 | 1006 |
| 10 | Title for 10 | 7 | 2025-01-01 04:40:12 | 5 | 2025-04-26 15:58:31 | 996 |
| 11 | Title for 11 | 9 | 2025-01-01 01:50:36 | 7 | 2025-04-26 17:35:13 | 1040 |
| 12 | Title for 12 | 9 | 2025-01-01 01:59:33 | 7 | 2025-04-26 14:36:15 | 957 |
| 13 | Title for 13 | 9 | 2025-01-01 00:36:36 | 1 | 2025-04-26 16:06:06 | 1012 |
| 14 | Title for 14 | 1 | 2025-01-01 00:10:03 | 9 | 2025-04-26 17:38:39 | 1021 |
| 15 | Title for 15 | 7 | 2025-01-01 04:20:13 | 9 | 2025-04-26 11:46:58 | 975 |
| 16 | Title for 16 | 7 | 2025-01-01 05:10:55 | 3 | 2025-04-25 23:02:15 | 1046 |
| 17 | Title for 17 | 1 | 2025-01-01 00:40:32 | 1 | 2025-04-26 16:05:11 | 990 |
| 18 | Title for 18 | 1 | 2025-01-01 01:36:39 | 5 | 2025-04-26 15:25:55 | 1001 |
| 19 | Title for 19 | 3 | 2025-01-01 03:01:40 | 9 | 2025-04-26 16:43:42 | 998 |
| 20 | Title for 20 | 5 | 2025-01-01 02:01:05 | 9 | 2025-04-26 03:49:55 | 999 |
| 21 | Title for 21 | 8 | 2025-01-01 00:06:03 | 6 | 2025-04-26 16:45:20 | 965 |
| 22 | Title for 22 | 6 | 2025-01-01 04:12:17 | 4 | 2025-04-26 14:16:07 | 959 |
| 23 | Title for 23 | 2 | 2025-01-01 00:12:36 | 4 | 2025-04-26 16:45:44 | 1061 |
| 24 | Title for 24 | 4 | 2025-01-01 01:21:15 | 4 | 2025-04-26 12:22:54 | 958 |
| 25 | Title for 25 | 4 | 2025-01-01 04:17:19 | 8 | 2025-04-26 09:01:11 | 1002 |
| 26 | Title for 26 | 4 | 2025-01-01 01:36:04 | 2 | 2025-04-26 16:48:51 | 926 |
| 27 | Title for 27 | 8 | 2025-01-01 00:48:38 | 8 | 2025-04-26 17:10:31 | 1039 |
| 28 | Title for 28 | 8 | 2025-01-01 00:10:47 | 2 | 2025-04-26 17:09:29 | 1003 |
| 29 | Title for 29 | 2 | 2025-01-01 01:26:19 | 8 | 2025-04-26 04:39:43 | 1016 |
| 30 | Title for 30 | 4 | 2025-01-01 02:53:39 | 10 | 2025-04-26 15:46:19 | 1051 |
| 31 | Title for 31 | 6 | 2025-01-01 01:53:55 | 10 | 2025-04-26 13:38:18 | 981 |
| 32 | Title for 32 | 10 | 2025-01-01 01:12:26 | 4 | 2025-04-26 16:19:21 | 1023 |
| 33 | Title for 33 | 6 | 2025-01-01 01:52:24 | 10 | 2025-04-26 13:10:21 | 1011 |
| 34 | Title for 34 | 10 | 2025-01-01 00:00:55 | 8 | 2025-04-26 16:51:41 | 975 |
| 35 | Title for 35 | 4 | 2025-01-01 00:46:17 | 6 | 2025-04-26 17:08:03 | 983 |
| 36 | Title for 36 | 10 | 2025-01-01 03:46:36 | 4 | 2025-04-26 16:55:28 | 932 |
| 37 | Title for 37 | 10 | 2025-01-01 00:20:37 | 2 | 2025-04-26 12:24:06 | 1019 |
| 38 | Title for 38 | 8 | 2025-01-01 02:29:19 | 4 | 2025-04-26 16:21:26 | 978 |
| 39 | Title for 39 | 10 | 2025-01-01 00:21:35 | 4 | 2025-04-26 13:44:21 | 948 |
| 40 | Title for 40 | 6 | 2025-01-01 00:31:16 | 2 | 2025-04-26 14:33:55 | 983 |
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 | 3 | 2025-01-01 01:04:05 | 7 | 2025-04-26 17:13:50 | 1011 |
| 6 | Some foxy stuff | 3 | 2025-01-01 05:26:31 | 5 | 2025-04-26 17:36:24 | 985 |
| 7 | This is a test | 5 | 2025-01-01 01:15:46 | 3 | 2025-04-26 11:07:43 | 1054 |
| 8 | Added title first | 7 | 2025-01-01 02:28:32 | 9 | 2025-04-26 17:11:49 | 1032 |
| 9 | Title for 9 | 3 | 2025-01-01 00:39:25 | 1 | 2025-04-26 14:22:45 | 1006 |
| 10 | Title for 10 | 7 | 2025-01-01 04:40:12 | 5 | 2025-04-26 15:58:31 | 996 |
| 11 | Title for 11 | 9 | 2025-01-01 01:50:36 | 7 | 2025-04-26 17:35:13 | 1040 |
| 12 | Title for 12 | 9 | 2025-01-01 01:59:33 | 7 | 2025-04-26 14:36:15 | 957 |
| 13 | Title for 13 | 9 | 2025-01-01 00:36:36 | 1 | 2025-04-26 16:06:06 | 1012 |
| 14 | Title for 14 | 1 | 2025-01-01 00:10:03 | 9 | 2025-04-26 17:38:39 | 1021 |
| 15 | Title for 15 | 7 | 2025-01-01 04:20:13 | 9 | 2025-04-26 11:46:58 | 975 |
| 16 | Title for 16 | 7 | 2025-01-01 05:10:55 | 3 | 2025-04-25 23:02:15 | 1046 |
| 17 | Title for 17 | 1 | 2025-01-01 00:40:32 | 1 | 2025-04-26 16:05:11 | 990 |
| 18 | Title for 18 | 1 | 2025-01-01 01:36:39 | 5 | 2025-04-26 15:25:55 | 1001 |
| 19 | Title for 19 | 3 | 2025-01-01 03:01:40 | 9 | 2025-04-26 16:43:42 | 998 |
| 20 | Title for 20 | 5 | 2025-01-01 02:01:05 | 9 | 2025-04-26 03:49:55 | 999 |
| 21 | Title for 21 | 8 | 2025-01-01 00:06:03 | 6 | 2025-04-26 16:45:20 | 965 |
| 22 | Title for 22 | 6 | 2025-01-01 04:12:17 | 4 | 2025-04-26 14:16:07 | 959 |
| 23 | Title for 23 | 2 | 2025-01-01 00:12:36 | 4 | 2025-04-26 16:45:44 | 1061 |
| 24 | Title for 24 | 4 | 2025-01-01 01:21:15 | 4 | 2025-04-26 12:22:54 | 958 |
| 25 | Title for 25 | 4 | 2025-01-01 04:17:19 | 8 | 2025-04-26 09:01:11 | 1002 |
| 26 | Title for 26 | 4 | 2025-01-01 01:36:04 | 2 | 2025-04-26 16:48:51 | 926 |
| 27 | Title for 27 | 8 | 2025-01-01 00:48:38 | 8 | 2025-04-26 17:10:31 | 1039 |
| 28 | Title for 28 | 8 | 2025-01-01 00:10:47 | 2 | 2025-04-26 17:09:29 | 1003 |
| 29 | Title for 29 | 2 | 2025-01-01 01:26:19 | 8 | 2025-04-26 04:39:43 | 1016 |
| 30 | Title for 30 | 4 | 2025-01-01 02:53:39 | 10 | 2025-04-26 15:46:19 | 1051 |
| 31 | Title for 31 | 6 | 2025-01-01 01:53:55 | 10 | 2025-04-26 13:38:18 | 981 |
| 32 | Title for 32 | 10 | 2025-01-01 01:12:26 | 4 | 2025-04-26 16:19:21 | 1023 |
| 33 | Title for 33 | 6 | 2025-01-01 01:52:24 | 10 | 2025-04-26 13:10:21 | 1011 |
| 34 | Title for 34 | 10 | 2025-01-01 00:00:55 | 8 | 2025-04-26 16:51:41 | 975 |
| 35 | Title for 35 | 4 | 2025-01-01 00:46:17 | 6 | 2025-04-26 17:08:03 | 983 |
| 36 | Title for 36 | 10 | 2025-01-01 03:46:36 | 4 | 2025-04-26 16:55:28 | 932 |
| 37 | Title for 37 | 10 | 2025-01-01 00:20:37 | 2 | 2025-04-26 12:24:06 | 1019 |
| 38 | Title for 38 | 8 | 2025-01-01 02:29:19 | 4 | 2025-04-26 16:21:26 | 978 |
| 39 | Title for 39 | 10 | 2025-01-01 00:21:35 | 4 | 2025-04-26 13:44:21 | 948 |
| 40 | Title for 40 | 6 | 2025-01-01 00:31:16 | 2 | 2025-04-26 14:33:55 | 983 |
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 |
|---|---|---|---|---|---|---|
| 35 | Title for 35 | 4 | 2025-01-01 00:46:17 | 6 | 2025-04-26 17:08:03 | 983 |
| 8 | Added title first | 7 | 2025-01-01 02:28:32 | 9 | 2025-04-26 17:11:49 | 1032 |
| 36 | Title for 36 | 10 | 2025-01-01 03:46:36 | 4 | 2025-04-26 16:55:28 | 932 |
| 30 | Title for 30 | 4 | 2025-01-01 02:53:39 | 10 | 2025-04-26 15:46:19 | 1051 |
| 18 | Title for 18 | 1 | 2025-01-01 01:36:39 | 5 | 2025-04-26 15:25:55 | 1001 |
| 39 | Title for 39 | 10 | 2025-01-01 00:21:35 | 4 | 2025-04-26 13:44:21 | 948 |
| 37 | Title for 37 | 10 | 2025-01-01 00:20:37 | 2 | 2025-04-26 12:24:06 | 1019 |
| 5 | Another thread | 3 | 2025-01-01 01:04:05 | 7 | 2025-04-26 17:13:50 | 1011 |
| 32 | Title for 32 | 10 | 2025-01-01 01:12:26 | 4 | 2025-04-26 16:19:21 | 1023 |
| 25 | Title for 25 | 4 | 2025-01-01 04:17:19 | 8 | 2025-04-26 09:01:11 | 1002 |
| 38 | Title for 38 | 8 | 2025-01-01 02:29:19 | 4 | 2025-04-26 16:21:26 | 978 |
| 11 | Title for 11 | 9 | 2025-01-01 01:50:36 | 7 | 2025-04-26 17:35:13 | 1040 |
| 28 | Title for 28 | 8 | 2025-01-01 00:10:47 | 2 | 2025-04-26 17:09:29 | 1003 |
| 31 | Title for 31 | 6 | 2025-01-01 01:53:55 | 10 | 2025-04-26 13:38:18 | 981 |
| 9 | Title for 9 | 3 | 2025-01-01 00:39:25 | 1 | 2025-04-26 14:22:45 | 1006 |
| 33 | Title for 33 | 6 | 2025-01-01 01:52:24 | 10 | 2025-04-26 13:10:21 | 1011 |
| 20 | Title for 20 | 5 | 2025-01-01 02:01:05 | 9 | 2025-04-26 03:49:55 | 999 |
| 22 | Title for 22 | 6 | 2025-01-01 04:12:17 | 4 | 2025-04-26 14:16:07 | 959 |
| 34 | Title for 34 | 10 | 2025-01-01 00:00:55 | 8 | 2025-04-26 16:51:41 | 975 |
| 29 | Title for 29 | 2 | 2025-01-01 01:26:19 | 8 | 2025-04-26 04:39:43 | 1016 |
| 19 | Title for 19 | 3 | 2025-01-01 03:01:40 | 9 | 2025-04-26 16:43:42 | 998 |
| 40 | Title for 40 | 6 | 2025-01-01 00:31:16 | 2 | 2025-04-26 14:33:55 | 983 |
| 10 | Title for 10 | 7 | 2025-01-01 04:40:12 | 5 | 2025-04-26 15:58:31 | 996 |
| 17 | Title for 17 | 1 | 2025-01-01 00:40:32 | 1 | 2025-04-26 16:05:11 | 990 |
| 26 | Title for 26 | 4 | 2025-01-01 01:36:04 | 2 | 2025-04-26 16:48:51 | 926 |
| 21 | Title for 21 | 8 | 2025-01-01 00:06:03 | 6 | 2025-04-26 16:45:20 | 965 |
| 12 | Title for 12 | 9 | 2025-01-01 01:59:33 | 7 | 2025-04-26 14:36:15 | 957 |
| 6 | Some foxy stuff | 3 | 2025-01-01 05:26:31 | 5 | 2025-04-26 17:36:24 | 985 |
| 13 | Title for 13 | 9 | 2025-01-01 00:36:36 | 1 | 2025-04-26 16:06:06 | 1012 |
| 23 | Title for 23 | 2 | 2025-01-01 00:12:36 | 4 | 2025-04-26 16:45:44 | 1061 |
| 14 | Title for 14 | 1 | 2025-01-01 00:10:03 | 9 | 2025-04-26 17:38:39 | 1021 |
| 7 | This is a test | 5 | 2025-01-01 01:15:46 | 3 | 2025-04-26 11:07:43 | 1054 |
| 27 | Title for 27 | 8 | 2025-01-01 00:48:38 | 8 | 2025-04-26 17:10:31 | 1039 |
| 24 | Title for 24 | 4 | 2025-01-01 01:21:15 | 4 | 2025-04-26 12:22:54 | 958 |
| 15 | Title for 15 | 7 | 2025-01-01 04:20:13 | 9 | 2025-04-26 11:46:58 | 975 |
| 16 | Title for 16 | 7 | 2025-01-01 05:10:55 | 3 | 2025-04-25 23:02:15 | 1046 |
show profiles;
| Query_ID | Duration | Query |
|---|---|---|
| 1 | 0.11639850 | 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 b |
| 2 | 0.31902950 | 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 a |
| 3 | 0.07178500 | 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 |
create index ix_posts_thread_postdate on post (threadId,postDate);
Records: 0 Duplicates: 0 Warnings: 0
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 | 3 | 2025-01-01 01:04:05 | 7 | 2025-04-26 17:13:50 | 1011 |
| 6 | Some foxy stuff | 3 | 2025-01-01 05:26:31 | 5 | 2025-04-26 17:36:24 | 985 |
| 7 | This is a test | 5 | 2025-01-01 01:15:46 | 3 | 2025-04-26 11:07:43 | 1054 |
| 8 | Added title first | 7 | 2025-01-01 02:28:32 | 9 | 2025-04-26 17:11:49 | 1032 |
| 9 | Title for 9 | 3 | 2025-01-01 00:39:25 | 1 | 2025-04-26 14:22:45 | 1006 |
| 10 | Title for 10 | 7 | 2025-01-01 04:40:12 | 5 | 2025-04-26 15:58:31 | 996 |
| 11 | Title for 11 | 9 | 2025-01-01 01:50:36 | 7 | 2025-04-26 17:35:13 | 1040 |
| 12 | Title for 12 | 9 | 2025-01-01 01:59:33 | 7 | 2025-04-26 14:36:15 | 957 |
| 13 | Title for 13 | 9 | 2025-01-01 00:36:36 | 1 | 2025-04-26 16:06:06 | 1012 |
| 14 | Title for 14 | 1 | 2025-01-01 00:10:03 | 9 | 2025-04-26 17:38:39 | 1021 |
| 15 | Title for 15 | 7 | 2025-01-01 04:20:13 | 9 | 2025-04-26 11:46:58 | 975 |
| 16 | Title for 16 | 7 | 2025-01-01 05:10:55 | 3 | 2025-04-25 23:02:15 | 1046 |
| 17 | Title for 17 | 1 | 2025-01-01 00:40:32 | 1 | 2025-04-26 16:05:11 | 990 |
| 18 | Title for 18 | 1 | 2025-01-01 01:36:39 | 5 | 2025-04-26 15:25:55 | 1001 |
| 19 | Title for 19 | 3 | 2025-01-01 03:01:40 | 9 | 2025-04-26 16:43:42 | 998 |
| 20 | Title for 20 | 5 | 2025-01-01 02:01:05 | 9 | 2025-04-26 03:49:55 | 999 |
| 21 | Title for 21 | 8 | 2025-01-01 00:06:03 | 6 | 2025-04-26 16:45:20 | 965 |
| 22 | Title for 22 | 6 | 2025-01-01 04:12:17 | 4 | 2025-04-26 14:16:07 | 959 |
| 23 | Title for 23 | 2 | 2025-01-01 00:12:36 | 4 | 2025-04-26 16:45:44 | 1061 |
| 24 | Title for 24 | 4 | 2025-01-01 01:21:15 | 4 | 2025-04-26 12:22:54 | 958 |
| 25 | Title for 25 | 4 | 2025-01-01 04:17:19 | 8 | 2025-04-26 09:01:11 | 1002 |
| 26 | Title for 26 | 4 | 2025-01-01 01:36:04 | 2 | 2025-04-26 16:48:51 | 926 |
| 27 | Title for 27 | 8 | 2025-01-01 00:48:38 | 8 | 2025-04-26 17:10:31 | 1039 |
| 28 | Title for 28 | 8 | 2025-01-01 00:10:47 | 2 | 2025-04-26 17:09:29 | 1003 |
| 29 | Title for 29 | 2 | 2025-01-01 01:26:19 | 8 | 2025-04-26 04:39:43 | 1016 |
| 30 | Title for 30 | 4 | 2025-01-01 02:53:39 | 10 | 2025-04-26 15:46:19 | 1051 |
| 31 | Title for 31 | 6 | 2025-01-01 01:53:55 | 10 | 2025-04-26 13:38:18 | 981 |
| 32 | Title for 32 | 10 | 2025-01-01 01:12:26 | 4 | 2025-04-26 16:19:21 | 1023 |
| 33 | Title for 33 | 6 | 2025-01-01 01:52:24 | 10 | 2025-04-26 13:10:21 | 1011 |
| 34 | Title for 34 | 10 | 2025-01-01 00:00:55 | 8 | 2025-04-26 16:51:41 | 975 |
| 35 | Title for 35 | 4 | 2025-01-01 00:46:17 | 6 | 2025-04-26 17:08:03 | 983 |
| 36 | Title for 36 | 10 | 2025-01-01 03:46:36 | 4 | 2025-04-26 16:55:28 | 932 |
| 37 | Title for 37 | 10 | 2025-01-01 00:20:37 | 2 | 2025-04-26 12:24:06 | 1019 |
| 38 | Title for 38 | 8 | 2025-01-01 02:29:19 | 4 | 2025-04-26 16:21:26 | 978 |
| 39 | Title for 39 | 10 | 2025-01-01 00:21:35 | 4 | 2025-04-26 13:44:21 | 948 |
| 40 | Title for 40 | 6 | 2025-01-01 00:31:16 | 2 | 2025-04-26 14:33:55 | 983 |
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 | 3 | 2025-01-01 01:04:05 | 7 | 2025-04-26 17:13:50 | 1011 |
| 6 | Some foxy stuff | 3 | 2025-01-01 05:26:31 | 5 | 2025-04-26 17:36:24 | 985 |
| 7 | This is a test | 5 | 2025-01-01 01:15:46 | 3 | 2025-04-26 11:07:43 | 1054 |
| 8 | Added title first | 7 | 2025-01-01 02:28:32 | 9 | 2025-04-26 17:11:49 | 1032 |
| 9 | Title for 9 | 3 | 2025-01-01 00:39:25 | 1 | 2025-04-26 14:22:45 | 1006 |
| 10 | Title for 10 | 7 | 2025-01-01 04:40:12 | 5 | 2025-04-26 15:58:31 | 996 |
| 11 | Title for 11 | 9 | 2025-01-01 01:50:36 | 7 | 2025-04-26 17:35:13 | 1040 |
| 12 | Title for 12 | 9 | 2025-01-01 01:59:33 | 7 | 2025-04-26 14:36:15 | 957 |
| 13 | Title for 13 | 9 | 2025-01-01 00:36:36 | 1 | 2025-04-26 16:06:06 | 1012 |
| 14 | Title for 14 | 1 | 2025-01-01 00:10:03 | 9 | 2025-04-26 17:38:39 | 1021 |
| 15 | Title for 15 | 7 | 2025-01-01 04:20:13 | 9 | 2025-04-26 11:46:58 | 975 |
| 16 | Title for 16 | 7 | 2025-01-01 05:10:55 | 3 | 2025-04-25 23:02:15 | 1046 |
| 17 | Title for 17 | 1 | 2025-01-01 00:40:32 | 1 | 2025-04-26 16:05:11 | 990 |
| 18 | Title for 18 | 1 | 2025-01-01 01:36:39 | 5 | 2025-04-26 15:25:55 | 1001 |
| 19 | Title for 19 | 3 | 2025-01-01 03:01:40 | 9 | 2025-04-26 16:43:42 | 998 |
| 20 | Title for 20 | 5 | 2025-01-01 02:01:05 | 9 | 2025-04-26 03:49:55 | 999 |
| 21 | Title for 21 | 8 | 2025-01-01 00:06:03 | 6 | 2025-04-26 16:45:20 | 965 |
| 22 | Title for 22 | 6 | 2025-01-01 04:12:17 | 4 | 2025-04-26 14:16:07 | 959 |
| 23 | Title for 23 | 2 | 2025-01-01 00:12:36 | 4 | 2025-04-26 16:45:44 | 1061 |
| 24 | Title for 24 | 4 | 2025-01-01 01:21:15 | 4 | 2025-04-26 12:22:54 | 958 |
| 25 | Title for 25 | 4 | 2025-01-01 04:17:19 | 8 | 2025-04-26 09:01:11 | 1002 |
| 26 | Title for 26 | 4 | 2025-01-01 01:36:04 | 2 | 2025-04-26 16:48:51 | 926 |
| 27 | Title for 27 | 8 | 2025-01-01 00:48:38 | 8 | 2025-04-26 17:10:31 | 1039 |
| 28 | Title for 28 | 8 | 2025-01-01 00:10:47 | 2 | 2025-04-26 17:09:29 | 1003 |
| 29 | Title for 29 | 2 | 2025-01-01 01:26:19 | 8 | 2025-04-26 04:39:43 | 1016 |
| 30 | Title for 30 | 4 | 2025-01-01 02:53:39 | 10 | 2025-04-26 15:46:19 | 1051 |
| 31 | Title for 31 | 6 | 2025-01-01 01:53:55 | 10 | 2025-04-26 13:38:18 | 981 |
| 32 | Title for 32 | 10 | 2025-01-01 01:12:26 | 4 | 2025-04-26 16:19:21 | 1023 |
| 33 | Title for 33 | 6 | 2025-01-01 01:52:24 | 10 | 2025-04-26 13:10:21 | 1011 |
| 34 | Title for 34 | 10 | 2025-01-01 00:00:55 | 8 | 2025-04-26 16:51:41 | 975 |
| 35 | Title for 35 | 4 | 2025-01-01 00:46:17 | 6 | 2025-04-26 17:08:03 | 983 |
| 36 | Title for 36 | 10 | 2025-01-01 03:46:36 | 4 | 2025-04-26 16:55:28 | 932 |
| 37 | Title for 37 | 10 | 2025-01-01 00:20:37 | 2 | 2025-04-26 12:24:06 | 1019 |
| 38 | Title for 38 | 8 | 2025-01-01 02:29:19 | 4 | 2025-04-26 16:21:26 | 978 |
| 39 | Title for 39 | 10 | 2025-01-01 00:21:35 | 4 | 2025-04-26 13:44:21 | 948 |
| 40 | Title for 40 | 6 | 2025-01-01 00:31:16 | 2 | 2025-04-26 14:33:55 | 983 |
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 |
|---|---|---|---|---|---|---|
| 5 | Another thread | 3 | 2025-01-01 01:04:05 | 7 | 2025-04-26 17:13:50 | 1011 |
| 6 | Some foxy stuff | 3 | 2025-01-01 05:26:31 | 5 | 2025-04-26 17:36:24 | 985 |
| 7 | This is a test | 5 | 2025-01-01 01:15:46 | 3 | 2025-04-26 11:07:43 | 1054 |
| 8 | Added title first | 7 | 2025-01-01 02:28:32 | 9 | 2025-04-26 17:11:49 | 1032 |
| 9 | Title for 9 | 3 | 2025-01-01 00:39:25 | 1 | 2025-04-26 14:22:45 | 1006 |
| 10 | Title for 10 | 7 | 2025-01-01 04:40:12 | 5 | 2025-04-26 15:58:31 | 996 |
| 11 | Title for 11 | 9 | 2025-01-01 01:50:36 | 7 | 2025-04-26 17:35:13 | 1040 |
| 12 | Title for 12 | 9 | 2025-01-01 01:59:33 | 7 | 2025-04-26 14:36:15 | 957 |
| 13 | Title for 13 | 9 | 2025-01-01 00:36:36 | 1 | 2025-04-26 16:06:06 | 1012 |
| 14 | Title for 14 | 1 | 2025-01-01 00:10:03 | 9 | 2025-04-26 17:38:39 | 1021 |
| 15 | Title for 15 | 7 | 2025-01-01 04:20:13 | 9 | 2025-04-26 11:46:58 | 975 |
| 16 | Title for 16 | 7 | 2025-01-01 05:10:55 | 3 | 2025-04-25 23:02:15 | 1046 |
| 17 | Title for 17 | 1 | 2025-01-01 00:40:32 | 1 | 2025-04-26 16:05:11 | 990 |
| 18 | Title for 18 | 1 | 2025-01-01 01:36:39 | 5 | 2025-04-26 15:25:55 | 1001 |
| 19 | Title for 19 | 3 | 2025-01-01 03:01:40 | 9 | 2025-04-26 16:43:42 | 998 |
| 20 | Title for 20 | 5 | 2025-01-01 02:01:05 | 9 | 2025-04-26 03:49:55 | 999 |
| 21 | Title for 21 | 8 | 2025-01-01 00:06:03 | 6 | 2025-04-26 16:45:20 | 965 |
| 22 | Title for 22 | 6 | 2025-01-01 04:12:17 | 4 | 2025-04-26 14:16:07 | 959 |
| 23 | Title for 23 | 2 | 2025-01-01 00:12:36 | 4 | 2025-04-26 16:45:44 | 1061 |
| 24 | Title for 24 | 4 | 2025-01-01 01:21:15 | 4 | 2025-04-26 12:22:54 | 958 |
| 25 | Title for 25 | 4 | 2025-01-01 04:17:19 | 8 | 2025-04-26 09:01:11 | 1002 |
| 26 | Title for 26 | 4 | 2025-01-01 01:36:04 | 2 | 2025-04-26 16:48:51 | 926 |
| 27 | Title for 27 | 8 | 2025-01-01 00:48:38 | 8 | 2025-04-26 17:10:31 | 1039 |
| 28 | Title for 28 | 8 | 2025-01-01 00:10:47 | 2 | 2025-04-26 17:09:29 | 1003 |
| 29 | Title for 29 | 2 | 2025-01-01 01:26:19 | 8 | 2025-04-26 04:39:43 | 1016 |
| 30 | Title for 30 | 4 | 2025-01-01 02:53:39 | 10 | 2025-04-26 15:46:19 | 1051 |
| 31 | Title for 31 | 6 | 2025-01-01 01:53:55 | 10 | 2025-04-26 13:38:18 | 981 |
| 32 | Title for 32 | 10 | 2025-01-01 01:12:26 | 4 | 2025-04-26 16:19:21 | 1023 |
| 33 | Title for 33 | 6 | 2025-01-01 01:52:24 | 10 | 2025-04-26 13:10:21 | 1011 |
| 34 | Title for 34 | 10 | 2025-01-01 00:00:55 | 8 | 2025-04-26 16:51:41 | 975 |
| 35 | Title for 35 | 4 | 2025-01-01 00:46:17 | 6 | 2025-04-26 17:08:03 | 983 |
| 36 | Title for 36 | 10 | 2025-01-01 03:46:36 | 4 | 2025-04-26 16:55:28 | 932 |
| 37 | Title for 37 | 10 | 2025-01-01 00:20:37 | 2 | 2025-04-26 12:24:06 | 1019 |
| 38 | Title for 38 | 8 | 2025-01-01 02:29:19 | 4 | 2025-04-26 16:21:26 | 978 |
| 39 | Title for 39 | 10 | 2025-01-01 00:21:35 | 4 | 2025-04-26 13:44:21 | 948 |
| 40 | Title for 40 | 6 | 2025-01-01 00:31:16 | 2 | 2025-04-26 14:33:55 | 983 |
show profiles;
| Query_ID | Duration | Query |
|---|---|---|
| 1 | 0.11639850 | 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 b |
| 2 | 0.31902950 | 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 a |
| 3 | 0.07178500 | 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 |
| 4 | 0.13194325 | create index ix_posts_thread_postdate on post (threadId,postDate) |
| 5 | 0.11285575 | 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 b |
| 6 | 0.42199625 | 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 a |
| 7 | 0.01762075 | 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 |
explain
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 | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | PRIMARY | t | null | ALL | null | null | null | null | 36 | 100.00 | null |
| 1 | PRIMARY | <derived3> | null | ref | <auto_key0> | <auto_key0> | 12 | fiddle.t.id,const | 401 | 100.00 | Using where |
| 1 | PRIMARY | <derived5> | null | ref | <auto_key0> | <auto_key0> | 4 | fiddle.t.id | 401 | 100.00 | Using where |
| 5 | DERIVED | post | null | ALL | null | null | null | null | 40000 | 100.00 | Using filesort |
| 3 | DERIVED | post | null | ALL | null | null | null | null | 40000 | 100.00 | Using filesort |
explain
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 | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | PRIMARY | t | null | ALL | null | null | null | null | 36 | 100.00 | null |
| 1 | PRIMARY | <derived2> | null | ref | <auto_key0> | <auto_key0> | 4 | fiddle.t.id | 75 | 100.00 | null |
| 2 | DERIVED | <derived3> | null | ALL | null | null | null | null | 40000 | 19.00 | Using where; Using temporary |
| 3 | DERIVED | post | null | ALL | null | null | null | null | 40000 | 100.00 | Using filesort |
explain
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 | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | PRIMARY | t | null | ALL | null | null | null | null | 36 | 100.00 | null |
| 1 | PRIMARY | <derived2> | null | ref | <auto_key0> | <auto_key0> | 4 | fiddle.t.id | 401 | 100.00 | null |
| 1 | PRIMARY | p1 | null | ref | ix_posts_thread_postdate | ix_posts_thread_postdate | 9 | fiddle.t.id,fl.pFirst | 1 | 100.00 | null |
| 1 | PRIMARY | p2 | null | ref | ix_posts_thread_postdate | ix_posts_thread_postdate | 9 | fiddle.t.id,fl.pLast | 1 | 100.00 | null |
| 2 | DERIVED | post | null | index | ix_posts_thread_postdate | ix_posts_thread_postdate | 9 | null | 40000 | 100.00 | Using index |