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 |
|---|---|---|---|---|---|---|
| 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 |
-- 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
Unknown system variable 'cte_max_recursion_depth'
-- 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<4000
)
select postDate,userId,threadId from pr;
-- select * from post;
Records: 3997 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 | 5 | 2025-01-02 18:47:04 | 5 | 2025-04-26 02:34:09 | 104 |
| 6 | Some foxy stuff | 9 | 2025-01-01 21:27:15 | 1 | 2025-04-24 20:51:06 | 83 |
| 7 | This is a test | 7 | 2025-01-01 19:14:48 | 7 | 2025-04-26 01:21:00 | 95 |
| 8 | Added title first | 5 | 2025-01-05 08:51:29 | 9 | 2025-04-25 22:15:06 | 97 |
| 9 | Title for 9 | 5 | 2025-01-01 05:21:31 | 7 | 2025-04-26 16:38:20 | 84 |
| 10 | Title for 10 | 3 | 2025-01-01 04:46:11 | 5 | 2025-04-25 22:15:26 | 99 |
| 11 | Title for 11 | 7 | 2025-01-01 04:48:18 | 7 | 2025-04-26 05:13:17 | 97 |
| 12 | Title for 12 | 5 | 2025-01-02 18:51:47 | 5 | 2025-04-26 15:13:07 | 96 |
| 13 | Title for 13 | 3 | 2025-01-01 05:50:57 | 7 | 2025-04-26 12:54:19 | 95 |
| 14 | Title for 14 | 5 | 2025-01-01 18:03:54 | 7 | 2025-04-26 17:28:00 | 116 |
| 15 | Title for 15 | 3 | 2025-01-03 18:08:20 | 9 | 2025-04-25 22:08:58 | 112 |
| 16 | Title for 16 | 5 | 2025-01-03 16:14:00 | 3 | 2025-04-25 17:06:35 | 94 |
| 17 | Title for 17 | 9 | 2025-01-01 00:41:42 | 1 | 2025-04-21 05:48:56 | 98 |
| 18 | Title for 18 | 1 | 2025-01-03 00:09:12 | 9 | 2025-04-26 12:14:34 | 95 |
| 19 | Title for 19 | 6 | 2025-01-03 18:55:40 | 3 | 2025-04-24 15:36:21 | 94 |
| 20 | Title for 20 | 7 | 2025-01-01 03:12:07 | 1 | 2025-04-26 16:52:48 | 101 |
| 21 | Title for 21 | 8 | 2025-01-01 02:38:30 | 7 | 2025-04-25 08:50:51 | 104 |
| 22 | Title for 22 | 8 | 2025-01-01 23:50:17 | 2 | 2025-04-25 13:56:48 | 113 |
| 23 | Title for 23 | 8 | 2025-01-01 20:38:30 | 6 | 2025-04-25 03:27:53 | 89 |
| 24 | Title for 24 | 4 | 2025-01-01 12:38:04 | 10 | 2025-04-26 05:19:51 | 101 |
| 25 | Title for 25 | 8 | 2025-01-03 07:11:01 | 8 | 2025-04-26 11:57:00 | 108 |
| 26 | Title for 26 | 10 | 2025-01-01 13:13:38 | 8 | 2025-04-24 13:44:15 | 115 |
| 27 | Title for 27 | 8 | 2025-01-02 06:03:18 | 8 | 2025-04-25 14:10:47 | 84 |
| 28 | Title for 28 | 10 | 2025-01-01 09:24:29 | 6 | 2025-04-26 04:37:22 | 100 |
| 29 | Title for 29 | 4 | 2025-01-01 16:44:42 | 8 | 2025-04-25 04:46:48 | 111 |
| 30 | Title for 30 | 6 | 2025-01-03 22:39:41 | 2 | 2025-04-26 05:28:37 | 94 |
| 31 | Title for 31 | 4 | 2025-01-02 13:52:12 | 6 | 2025-04-26 16:17:01 | 97 |
| 32 | Title for 32 | 4 | 2025-01-01 10:44:37 | 10 | 2025-04-26 09:51:30 | 106 |
| 33 | Title for 33 | 4 | 2025-01-01 03:09:17 | 8 | 2025-04-26 10:53:33 | 104 |
| 34 | Title for 34 | 2 | 2025-01-01 00:55:47 | 2 | 2025-04-23 23:20:10 | 100 |
| 35 | Title for 35 | 8 | 2025-01-01 17:19:32 | 8 | 2025-04-26 12:19:53 | 103 |
| 36 | Title for 36 | 2 | 2025-01-01 01:27:38 | 2 | 2025-04-26 10:10:21 | 121 |
| 37 | Title for 37 | 4 | 2025-01-01 03:29:18 | 2 | 2025-04-24 11:34:12 | 103 |
| 38 | Title for 38 | 6 | 2025-01-01 06:55:51 | 2 | 2025-04-26 16:55:26 | 100 |
| 39 | Title for 39 | 2 | 2025-01-01 18:57:15 | 2 | 2025-04-26 13:33:10 | 106 |
| 40 | Title for 40 | 4 | 2025-01-01 00:40:01 | 10 | 2025-04-26 09:15:30 | 96 |
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 | 5 | 2025-01-02 18:47:04 | 5 | 2025-04-26 02:34:09 | 104 |
| 6 | Some foxy stuff | 9 | 2025-01-01 21:27:15 | 1 | 2025-04-24 20:51:06 | 83 |
| 7 | This is a test | 7 | 2025-01-01 19:14:48 | 7 | 2025-04-26 01:21:00 | 95 |
| 8 | Added title first | 5 | 2025-01-05 08:51:29 | 9 | 2025-04-25 22:15:06 | 97 |
| 9 | Title for 9 | 5 | 2025-01-01 05:21:31 | 7 | 2025-04-26 16:38:20 | 84 |
| 10 | Title for 10 | 3 | 2025-01-01 04:46:11 | 5 | 2025-04-25 22:15:26 | 99 |
| 11 | Title for 11 | 7 | 2025-01-01 04:48:18 | 7 | 2025-04-26 05:13:17 | 97 |
| 12 | Title for 12 | 5 | 2025-01-02 18:51:47 | 5 | 2025-04-26 15:13:07 | 96 |
| 13 | Title for 13 | 3 | 2025-01-01 05:50:57 | 7 | 2025-04-26 12:54:19 | 95 |
| 14 | Title for 14 | 5 | 2025-01-01 18:03:54 | 7 | 2025-04-26 17:28:00 | 116 |
| 15 | Title for 15 | 3 | 2025-01-03 18:08:20 | 9 | 2025-04-25 22:08:58 | 112 |
| 16 | Title for 16 | 5 | 2025-01-03 16:14:00 | 3 | 2025-04-25 17:06:35 | 94 |
| 17 | Title for 17 | 9 | 2025-01-01 00:41:42 | 1 | 2025-04-21 05:48:56 | 98 |
| 18 | Title for 18 | 1 | 2025-01-03 00:09:12 | 9 | 2025-04-26 12:14:34 | 95 |
| 19 | Title for 19 | 6 | 2025-01-03 18:55:40 | 3 | 2025-04-24 15:36:21 | 94 |
| 20 | Title for 20 | 7 | 2025-01-01 03:12:07 | 1 | 2025-04-26 16:52:48 | 101 |
| 21 | Title for 21 | 8 | 2025-01-01 02:38:30 | 7 | 2025-04-25 08:50:51 | 104 |
| 22 | Title for 22 | 8 | 2025-01-01 23:50:17 | 2 | 2025-04-25 13:56:48 | 113 |
| 23 | Title for 23 | 8 | 2025-01-01 20:38:30 | 6 | 2025-04-25 03:27:53 | 89 |
| 24 | Title for 24 | 4 | 2025-01-01 12:38:04 | 10 | 2025-04-26 05:19:51 | 101 |
| 25 | Title for 25 | 8 | 2025-01-03 07:11:01 | 8 | 2025-04-26 11:57:00 | 108 |
| 26 | Title for 26 | 10 | 2025-01-01 13:13:38 | 8 | 2025-04-24 13:44:15 | 115 |
| 27 | Title for 27 | 8 | 2025-01-02 06:03:18 | 8 | 2025-04-25 14:10:47 | 84 |
| 28 | Title for 28 | 10 | 2025-01-01 09:24:29 | 6 | 2025-04-26 04:37:22 | 100 |
| 29 | Title for 29 | 4 | 2025-01-01 16:44:42 | 8 | 2025-04-25 04:46:48 | 111 |
| 30 | Title for 30 | 6 | 2025-01-03 22:39:41 | 2 | 2025-04-26 05:28:37 | 94 |
| 31 | Title for 31 | 4 | 2025-01-02 13:52:12 | 6 | 2025-04-26 16:17:01 | 97 |
| 32 | Title for 32 | 4 | 2025-01-01 10:44:37 | 10 | 2025-04-26 09:51:30 | 106 |
| 33 | Title for 33 | 4 | 2025-01-01 03:09:17 | 8 | 2025-04-26 10:53:33 | 104 |
| 34 | Title for 34 | 2 | 2025-01-01 00:55:47 | 2 | 2025-04-23 23:20:10 | 100 |
| 35 | Title for 35 | 8 | 2025-01-01 17:19:32 | 8 | 2025-04-26 12:19:53 | 103 |
| 36 | Title for 36 | 2 | 2025-01-01 01:27:38 | 2 | 2025-04-26 10:10:21 | 121 |
| 37 | Title for 37 | 4 | 2025-01-01 03:29:18 | 2 | 2025-04-24 11:34:12 | 103 |
| 38 | Title for 38 | 6 | 2025-01-01 06:55:51 | 2 | 2025-04-26 16:55:26 | 100 |
| 39 | Title for 39 | 2 | 2025-01-01 18:57:15 | 2 | 2025-04-26 13:33:10 | 106 |
| 40 | Title for 40 | 4 | 2025-01-01 00:40:01 | 10 | 2025-04-26 09:15:30 | 96 |
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 |
|---|---|---|---|---|---|---|
| 26 | Title for 26 | 10 | 2025-01-01 13:13:38 | 8 | 2025-04-24 13:44:15 | 115 |
| 23 | Title for 23 | 8 | 2025-01-01 20:38:30 | 6 | 2025-04-25 03:27:53 | 89 |
| 15 | Title for 15 | 3 | 2025-01-03 18:08:20 | 9 | 2025-04-25 22:08:58 | 112 |
| 38 | Title for 38 | 6 | 2025-01-01 06:55:51 | 2 | 2025-04-26 16:55:26 | 100 |
| 16 | Title for 16 | 5 | 2025-01-03 16:14:00 | 3 | 2025-04-25 17:06:35 | 94 |
| 30 | Title for 30 | 6 | 2025-01-03 22:39:41 | 2 | 2025-04-26 05:28:37 | 94 |
| 32 | Title for 32 | 4 | 2025-01-01 10:44:37 | 10 | 2025-04-26 09:51:30 | 106 |
| 28 | Title for 28 | 10 | 2025-01-01 09:24:29 | 6 | 2025-04-26 04:37:22 | 100 |
| 12 | Title for 12 | 5 | 2025-01-02 18:51:47 | 5 | 2025-04-26 15:13:07 | 96 |
| 14 | Title for 14 | 5 | 2025-01-01 18:03:54 | 7 | 2025-04-26 17:28:00 | 116 |
| 8 | Added title first | 5 | 2025-01-05 08:51:29 | 9 | 2025-04-25 22:15:06 | 97 |
| 24 | Title for 24 | 4 | 2025-01-01 12:38:04 | 10 | 2025-04-26 05:19:51 | 101 |
| 20 | Title for 20 | 7 | 2025-01-01 03:12:07 | 1 | 2025-04-26 16:52:48 | 101 |
| 34 | Title for 34 | 2 | 2025-01-01 00:55:47 | 2 | 2025-04-23 23:20:10 | 100 |
| 36 | Title for 36 | 2 | 2025-01-01 01:27:38 | 2 | 2025-04-26 10:10:21 | 121 |
| 21 | Title for 21 | 8 | 2025-01-01 02:38:30 | 7 | 2025-04-25 08:50:51 | 104 |
| 35 | Title for 35 | 8 | 2025-01-01 17:19:32 | 8 | 2025-04-26 12:19:53 | 103 |
| 22 | Title for 22 | 8 | 2025-01-01 23:50:17 | 2 | 2025-04-25 13:56:48 | 113 |
| 7 | This is a test | 7 | 2025-01-01 19:14:48 | 7 | 2025-04-26 01:21:00 | 95 |
| 11 | Title for 11 | 7 | 2025-01-01 04:48:18 | 7 | 2025-04-26 05:13:17 | 97 |
| 13 | Title for 13 | 3 | 2025-01-01 05:50:57 | 7 | 2025-04-26 12:54:19 | 95 |
| 19 | Title for 19 | 6 | 2025-01-03 18:55:40 | 3 | 2025-04-24 15:36:21 | 94 |
| 5 | Another thread | 5 | 2025-01-02 18:47:04 | 5 | 2025-04-26 02:34:09 | 104 |
| 18 | Title for 18 | 1 | 2025-01-03 00:09:12 | 9 | 2025-04-26 12:14:34 | 95 |
| 31 | Title for 31 | 4 | 2025-01-02 13:52:12 | 6 | 2025-04-26 16:17:01 | 97 |
| 27 | Title for 27 | 8 | 2025-01-02 06:03:18 | 8 | 2025-04-25 14:10:47 | 84 |
| 25 | Title for 25 | 8 | 2025-01-03 07:11:01 | 8 | 2025-04-26 11:57:00 | 108 |
| 10 | Title for 10 | 3 | 2025-01-01 04:46:11 | 5 | 2025-04-25 22:15:26 | 99 |
| 17 | Title for 17 | 9 | 2025-01-01 00:41:42 | 1 | 2025-04-21 05:48:56 | 98 |
| 40 | Title for 40 | 4 | 2025-01-01 00:40:01 | 10 | 2025-04-26 09:15:30 | 96 |
| 6 | Some foxy stuff | 9 | 2025-01-01 21:27:15 | 1 | 2025-04-24 20:51:06 | 83 |
| 39 | Title for 39 | 2 | 2025-01-01 18:57:15 | 2 | 2025-04-26 13:33:10 | 106 |
| 37 | Title for 37 | 4 | 2025-01-01 03:29:18 | 2 | 2025-04-24 11:34:12 | 103 |
| 33 | Title for 33 | 4 | 2025-01-01 03:09:17 | 8 | 2025-04-26 10:53:33 | 104 |
| 9 | Title for 9 | 5 | 2025-01-01 05:21:31 | 7 | 2025-04-26 16:38:20 | 84 |
| 29 | Title for 29 | 4 | 2025-01-01 16:44:42 | 8 | 2025-04-25 04:46:48 | 111 |
show profiles;
| Query_ID | Duration | Query |
|---|---|---|
| 1 | 0.01383719 | 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.02188532 | 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.11605299 | 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 | 5 | 2025-01-02 18:47:04 | 5 | 2025-04-26 02:34:09 | 104 |
| 6 | Some foxy stuff | 9 | 2025-01-01 21:27:15 | 1 | 2025-04-24 20:51:06 | 83 |
| 7 | This is a test | 7 | 2025-01-01 19:14:48 | 7 | 2025-04-26 01:21:00 | 95 |
| 8 | Added title first | 5 | 2025-01-05 08:51:29 | 9 | 2025-04-25 22:15:06 | 97 |
| 9 | Title for 9 | 5 | 2025-01-01 05:21:31 | 7 | 2025-04-26 16:38:20 | 84 |
| 10 | Title for 10 | 3 | 2025-01-01 04:46:11 | 5 | 2025-04-25 22:15:26 | 99 |
| 11 | Title for 11 | 7 | 2025-01-01 04:48:18 | 7 | 2025-04-26 05:13:17 | 97 |
| 12 | Title for 12 | 5 | 2025-01-02 18:51:47 | 5 | 2025-04-26 15:13:07 | 96 |
| 13 | Title for 13 | 3 | 2025-01-01 05:50:57 | 7 | 2025-04-26 12:54:19 | 95 |
| 14 | Title for 14 | 5 | 2025-01-01 18:03:54 | 7 | 2025-04-26 17:28:00 | 116 |
| 15 | Title for 15 | 3 | 2025-01-03 18:08:20 | 9 | 2025-04-25 22:08:58 | 112 |
| 16 | Title for 16 | 5 | 2025-01-03 16:14:00 | 3 | 2025-04-25 17:06:35 | 94 |
| 17 | Title for 17 | 9 | 2025-01-01 00:41:42 | 1 | 2025-04-21 05:48:56 | 98 |
| 18 | Title for 18 | 1 | 2025-01-03 00:09:12 | 9 | 2025-04-26 12:14:34 | 95 |
| 19 | Title for 19 | 6 | 2025-01-03 18:55:40 | 3 | 2025-04-24 15:36:21 | 94 |
| 20 | Title for 20 | 7 | 2025-01-01 03:12:07 | 1 | 2025-04-26 16:52:48 | 101 |
| 21 | Title for 21 | 8 | 2025-01-01 02:38:30 | 7 | 2025-04-25 08:50:51 | 104 |
| 22 | Title for 22 | 8 | 2025-01-01 23:50:17 | 2 | 2025-04-25 13:56:48 | 113 |
| 23 | Title for 23 | 8 | 2025-01-01 20:38:30 | 6 | 2025-04-25 03:27:53 | 89 |
| 24 | Title for 24 | 4 | 2025-01-01 12:38:04 | 10 | 2025-04-26 05:19:51 | 101 |
| 25 | Title for 25 | 8 | 2025-01-03 07:11:01 | 8 | 2025-04-26 11:57:00 | 108 |
| 26 | Title for 26 | 10 | 2025-01-01 13:13:38 | 8 | 2025-04-24 13:44:15 | 115 |
| 27 | Title for 27 | 8 | 2025-01-02 06:03:18 | 8 | 2025-04-25 14:10:47 | 84 |
| 28 | Title for 28 | 10 | 2025-01-01 09:24:29 | 6 | 2025-04-26 04:37:22 | 100 |
| 29 | Title for 29 | 4 | 2025-01-01 16:44:42 | 8 | 2025-04-25 04:46:48 | 111 |
| 30 | Title for 30 | 6 | 2025-01-03 22:39:41 | 2 | 2025-04-26 05:28:37 | 94 |
| 31 | Title for 31 | 4 | 2025-01-02 13:52:12 | 6 | 2025-04-26 16:17:01 | 97 |
| 32 | Title for 32 | 4 | 2025-01-01 10:44:37 | 10 | 2025-04-26 09:51:30 | 106 |
| 33 | Title for 33 | 4 | 2025-01-01 03:09:17 | 8 | 2025-04-26 10:53:33 | 104 |
| 34 | Title for 34 | 2 | 2025-01-01 00:55:47 | 2 | 2025-04-23 23:20:10 | 100 |
| 35 | Title for 35 | 8 | 2025-01-01 17:19:32 | 8 | 2025-04-26 12:19:53 | 103 |
| 36 | Title for 36 | 2 | 2025-01-01 01:27:38 | 2 | 2025-04-26 10:10:21 | 121 |
| 37 | Title for 37 | 4 | 2025-01-01 03:29:18 | 2 | 2025-04-24 11:34:12 | 103 |
| 38 | Title for 38 | 6 | 2025-01-01 06:55:51 | 2 | 2025-04-26 16:55:26 | 100 |
| 39 | Title for 39 | 2 | 2025-01-01 18:57:15 | 2 | 2025-04-26 13:33:10 | 106 |
| 40 | Title for 40 | 4 | 2025-01-01 00:40:01 | 10 | 2025-04-26 09:15:30 | 96 |
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 | 5 | 2025-01-02 18:47:04 | 5 | 2025-04-26 02:34:09 | 104 |
| 6 | Some foxy stuff | 9 | 2025-01-01 21:27:15 | 1 | 2025-04-24 20:51:06 | 83 |
| 7 | This is a test | 7 | 2025-01-01 19:14:48 | 7 | 2025-04-26 01:21:00 | 95 |
| 8 | Added title first | 5 | 2025-01-05 08:51:29 | 9 | 2025-04-25 22:15:06 | 97 |
| 9 | Title for 9 | 5 | 2025-01-01 05:21:31 | 7 | 2025-04-26 16:38:20 | 84 |
| 10 | Title for 10 | 3 | 2025-01-01 04:46:11 | 5 | 2025-04-25 22:15:26 | 99 |
| 11 | Title for 11 | 7 | 2025-01-01 04:48:18 | 7 | 2025-04-26 05:13:17 | 97 |
| 12 | Title for 12 | 5 | 2025-01-02 18:51:47 | 5 | 2025-04-26 15:13:07 | 96 |
| 13 | Title for 13 | 3 | 2025-01-01 05:50:57 | 7 | 2025-04-26 12:54:19 | 95 |
| 14 | Title for 14 | 5 | 2025-01-01 18:03:54 | 7 | 2025-04-26 17:28:00 | 116 |
| 15 | Title for 15 | 3 | 2025-01-03 18:08:20 | 9 | 2025-04-25 22:08:58 | 112 |
| 16 | Title for 16 | 5 | 2025-01-03 16:14:00 | 3 | 2025-04-25 17:06:35 | 94 |
| 17 | Title for 17 | 9 | 2025-01-01 00:41:42 | 1 | 2025-04-21 05:48:56 | 98 |
| 18 | Title for 18 | 1 | 2025-01-03 00:09:12 | 9 | 2025-04-26 12:14:34 | 95 |
| 19 | Title for 19 | 6 | 2025-01-03 18:55:40 | 3 | 2025-04-24 15:36:21 | 94 |
| 20 | Title for 20 | 7 | 2025-01-01 03:12:07 | 1 | 2025-04-26 16:52:48 | 101 |
| 21 | Title for 21 | 8 | 2025-01-01 02:38:30 | 7 | 2025-04-25 08:50:51 | 104 |
| 22 | Title for 22 | 8 | 2025-01-01 23:50:17 | 2 | 2025-04-25 13:56:48 | 113 |
| 23 | Title for 23 | 8 | 2025-01-01 20:38:30 | 6 | 2025-04-25 03:27:53 | 89 |
| 24 | Title for 24 | 4 | 2025-01-01 12:38:04 | 10 | 2025-04-26 05:19:51 | 101 |
| 25 | Title for 25 | 8 | 2025-01-03 07:11:01 | 8 | 2025-04-26 11:57:00 | 108 |
| 26 | Title for 26 | 10 | 2025-01-01 13:13:38 | 8 | 2025-04-24 13:44:15 | 115 |
| 27 | Title for 27 | 8 | 2025-01-02 06:03:18 | 8 | 2025-04-25 14:10:47 | 84 |
| 28 | Title for 28 | 10 | 2025-01-01 09:24:29 | 6 | 2025-04-26 04:37:22 | 100 |
| 29 | Title for 29 | 4 | 2025-01-01 16:44:42 | 8 | 2025-04-25 04:46:48 | 111 |
| 30 | Title for 30 | 6 | 2025-01-03 22:39:41 | 2 | 2025-04-26 05:28:37 | 94 |
| 31 | Title for 31 | 4 | 2025-01-02 13:52:12 | 6 | 2025-04-26 16:17:01 | 97 |
| 32 | Title for 32 | 4 | 2025-01-01 10:44:37 | 10 | 2025-04-26 09:51:30 | 106 |
| 33 | Title for 33 | 4 | 2025-01-01 03:09:17 | 8 | 2025-04-26 10:53:33 | 104 |
| 34 | Title for 34 | 2 | 2025-01-01 00:55:47 | 2 | 2025-04-23 23:20:10 | 100 |
| 35 | Title for 35 | 8 | 2025-01-01 17:19:32 | 8 | 2025-04-26 12:19:53 | 103 |
| 36 | Title for 36 | 2 | 2025-01-01 01:27:38 | 2 | 2025-04-26 10:10:21 | 121 |
| 37 | Title for 37 | 4 | 2025-01-01 03:29:18 | 2 | 2025-04-24 11:34:12 | 103 |
| 38 | Title for 38 | 6 | 2025-01-01 06:55:51 | 2 | 2025-04-26 16:55:26 | 100 |
| 39 | Title for 39 | 2 | 2025-01-01 18:57:15 | 2 | 2025-04-26 13:33:10 | 106 |
| 40 | Title for 40 | 4 | 2025-01-01 00:40:01 | 10 | 2025-04-26 09:15:30 | 96 |
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 | 5 | 2025-01-02 18:47:04 | 5 | 2025-04-26 02:34:09 | 104 |
| 6 | Some foxy stuff | 9 | 2025-01-01 21:27:15 | 1 | 2025-04-24 20:51:06 | 83 |
| 7 | This is a test | 7 | 2025-01-01 19:14:48 | 7 | 2025-04-26 01:21:00 | 95 |
| 8 | Added title first | 5 | 2025-01-05 08:51:29 | 9 | 2025-04-25 22:15:06 | 97 |
| 9 | Title for 9 | 5 | 2025-01-01 05:21:31 | 7 | 2025-04-26 16:38:20 | 84 |
| 10 | Title for 10 | 3 | 2025-01-01 04:46:11 | 5 | 2025-04-25 22:15:26 | 99 |
| 11 | Title for 11 | 7 | 2025-01-01 04:48:18 | 7 | 2025-04-26 05:13:17 | 97 |
| 12 | Title for 12 | 5 | 2025-01-02 18:51:47 | 5 | 2025-04-26 15:13:07 | 96 |
| 13 | Title for 13 | 3 | 2025-01-01 05:50:57 | 7 | 2025-04-26 12:54:19 | 95 |
| 14 | Title for 14 | 5 | 2025-01-01 18:03:54 | 7 | 2025-04-26 17:28:00 | 116 |
| 15 | Title for 15 | 3 | 2025-01-03 18:08:20 | 9 | 2025-04-25 22:08:58 | 112 |
| 16 | Title for 16 | 5 | 2025-01-03 16:14:00 | 3 | 2025-04-25 17:06:35 | 94 |
| 17 | Title for 17 | 9 | 2025-01-01 00:41:42 | 1 | 2025-04-21 05:48:56 | 98 |
| 18 | Title for 18 | 1 | 2025-01-03 00:09:12 | 9 | 2025-04-26 12:14:34 | 95 |
| 19 | Title for 19 | 6 | 2025-01-03 18:55:40 | 3 | 2025-04-24 15:36:21 | 94 |
| 20 | Title for 20 | 7 | 2025-01-01 03:12:07 | 1 | 2025-04-26 16:52:48 | 101 |
| 21 | Title for 21 | 8 | 2025-01-01 02:38:30 | 7 | 2025-04-25 08:50:51 | 104 |
| 22 | Title for 22 | 8 | 2025-01-01 23:50:17 | 2 | 2025-04-25 13:56:48 | 113 |
| 23 | Title for 23 | 8 | 2025-01-01 20:38:30 | 6 | 2025-04-25 03:27:53 | 89 |
| 24 | Title for 24 | 4 | 2025-01-01 12:38:04 | 10 | 2025-04-26 05:19:51 | 101 |
| 25 | Title for 25 | 8 | 2025-01-03 07:11:01 | 8 | 2025-04-26 11:57:00 | 108 |
| 26 | Title for 26 | 10 | 2025-01-01 13:13:38 | 8 | 2025-04-24 13:44:15 | 115 |
| 27 | Title for 27 | 8 | 2025-01-02 06:03:18 | 8 | 2025-04-25 14:10:47 | 84 |
| 28 | Title for 28 | 10 | 2025-01-01 09:24:29 | 6 | 2025-04-26 04:37:22 | 100 |
| 29 | Title for 29 | 4 | 2025-01-01 16:44:42 | 8 | 2025-04-25 04:46:48 | 111 |
| 30 | Title for 30 | 6 | 2025-01-03 22:39:41 | 2 | 2025-04-26 05:28:37 | 94 |
| 31 | Title for 31 | 4 | 2025-01-02 13:52:12 | 6 | 2025-04-26 16:17:01 | 97 |
| 32 | Title for 32 | 4 | 2025-01-01 10:44:37 | 10 | 2025-04-26 09:51:30 | 106 |
| 33 | Title for 33 | 4 | 2025-01-01 03:09:17 | 8 | 2025-04-26 10:53:33 | 104 |
| 34 | Title for 34 | 2 | 2025-01-01 00:55:47 | 2 | 2025-04-23 23:20:10 | 100 |
| 35 | Title for 35 | 8 | 2025-01-01 17:19:32 | 8 | 2025-04-26 12:19:53 | 103 |
| 36 | Title for 36 | 2 | 2025-01-01 01:27:38 | 2 | 2025-04-26 10:10:21 | 121 |
| 37 | Title for 37 | 4 | 2025-01-01 03:29:18 | 2 | 2025-04-24 11:34:12 | 103 |
| 38 | Title for 38 | 6 | 2025-01-01 06:55:51 | 2 | 2025-04-26 16:55:26 | 100 |
| 39 | Title for 39 | 2 | 2025-01-01 18:57:15 | 2 | 2025-04-26 13:33:10 | 106 |
| 40 | Title for 40 | 4 | 2025-01-01 00:40:01 | 10 | 2025-04-26 09:15:30 | 96 |
show profiles;
| Query_ID | Duration | Query |
|---|---|---|
| 1 | 0.01383719 | 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.02188532 | 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.11605299 | 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.02193853 | create index ix_posts_thread_postdate on post (threadId,postDate) |
| 5 | 0.01342206 | 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.02376043 | 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.01068157 | 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 | type | possible_keys | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | PRIMARY | t | ALL | null | null | null | null | 36 | |
| 1 | PRIMARY | <derived3> | ref | key0 | key0 | 4 | fiddle.t.id | 40 | Using where |
| 1 | PRIMARY | <derived4> | ref | key0 | key0 | 4 | fiddle.t.id | 40 | Using where |
| 4 | DERIVED | post | ALL | null | null | null | null | 4000 | Using temporary |
| 3 | DERIVED | post | ALL | null | null | null | null | 4000 | Using temporary |
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 | type | possible_keys | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | PRIMARY | t | ALL | null | null | null | null | 36 | |
| 1 | PRIMARY | <derived2> | ref | key0 | key0 | 5 | fiddle.t.id | 40 | |
| 2 | DERIVED | <derived3> | ALL | null | null | null | null | 4000 | Using where; Using temporary; Using filesort |
| 3 | DERIVED | post | ALL | null | null | null | null | 4000 | Using temporary |
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 | type | possible_keys | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | PRIMARY | t | ALL | null | null | null | null | 36 | |
| 1 | PRIMARY | <derived2> | ref | key0 | key0 | 5 | fiddle.t.id | 40 | |
| 1 | PRIMARY | p1 | ref | ix_posts_thread_postdate | ix_posts_thread_postdate | 9 | fiddle.t.id,fl.pFirst | 1 | Using where |
| 1 | PRIMARY | p2 | ref | ix_posts_thread_postdate | ix_posts_thread_postdate | 9 | fiddle.t.id,fl.pLast | 1 | Using where |
| 2 | DERIVED | post | index | ix_posts_thread_postdate | ix_posts_thread_postdate | 9 | null | 4000 | Using index; Using temporary; Using filesort |