add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();
version()
8.0.30
CREATE TABLE test
(
t_id INTEGER NOT NULL PRIMARY KEY,
status_to VARCHAR (50)
);
CREATE INDEX st_ix ON test (status_to);
Records: 0  Duplicates: 0  Warnings: 0
INSERT INTO test VALUES
(7, NULL), (8, 'Test_val_1'), (9, 'Test_val_2'),
(3, 'Publish'), (4, 'Action'), (5, 'Sold'), (6, 'Let'), (10, 'Draft'), (11, 'Unpublish'),
(12, 'Publish'), (13, 'Action'), (14, 'Sold'), (15, 'Let'), (26, 'Draft'), (16, 'Unpublish'),
(17, 'Publish'), (18, 'Action'), (19, 'Sold'), (20, 'Let'), (27, 'Draft'), (21, 'Unpublish'),
(22, 'Publish'), (23, 'Action'), (24, 'Sold'), (25, 'Let'), (1, 'Draft'), (2, 'Unpublish'),
(29, NULL), (30, NULL), (35, 'Test_val_1'), (31, 'Test_val_2'),
(45, 'Single_1'), (46, 'Single_2');

Records: 33  Duplicates: 0  Warnings: 0
SELECT
t_id, status_to,
ROW_NUMBER() OVER (PARTITION BY status_to ORDER BY status_to) AS rn
FROM test
WHERE status_to IS NOT NULL
ORDER BY status_to, t_id
t_id status_to rn
4 Action 4
13 Action 1
18 Action 2
23 Action 3
1 Draft 1
10 Draft 2
26 Draft 3
27 Draft 4
6 Let 1
15 Let 2
20 Let 3
25 Let 4
3 Publish 4
12 Publish 3
17 Publish 2
22 Publish 1
45 Single_1 1
46 Single_2 1
5 Sold 1
14 Sold 2
19 Sold 3
24 Sold 4
8 Test_val_1 1
35 Test_val_1 2
9 Test_val_2 1
31 Test_val_2 2
2 Unpublish 1
11 Unpublish 2
16 Unpublish 3
21 Unpublish 4
SELECT COUNT(rn) OVER (PARTITION BY rn) AS "Unique count"
FROM
(
SELECT
t_id, status_to,
ROW_NUMBER() OVER (PARTITION BY status_to ORDER BY status_to) AS rn
FROM test
WHERE status_to IS NOT NULL
ORDER BY status_to, t_id
) AS tab
WHERE rn > 1
GROUP BY status_to
ORDER BY status_to
LIMIT 1;
Unique count
2
SELECT status_to, MAX(rn)
FROM
(
SELECT
t_id, status_to,
ROW_NUMBER() OVER (PARTITION BY status_to ORDER BY status_to) AS rn
FROM test
WHERE status_to IS NOT NULL
ORDER BY status_to, t_id
) AS tab
GROUP BY status_to
HAVING MAX(rn) > 1
ORDER BY status_to;
status_to MAX(rn)
Action 4
Draft 4
Let 4
Publish 4
Sold 4
Test_val_1 2
Test_val_2 2
Unpublish 4
SELECT
COUNT(DISTINCT(status_to)) AS "Unique count"
FROM
test;
Unique count
10
SELECT
COUNT(status_to) AS "Unique count"
FROM
test
WHERE status_to IS NOT NULL
GROUP BY status_to
HAVING COUNT(status_to) > 1;
Unique count
4
4
4
4
4
2
2
4
EXPLAIN ANALYZE
SELECT
COUNT(DISTINCT(status_to)) AS "Unique count"
FROM
test;
EXPLAIN
-> Aggregate: count(distinct test.status_to) (cost=6.80 rows=1) (actual time=0.034..0.034 rows=1 loops=1)
    -> Covering index skip scan for deduplication on test using st_ix (cost=3.40 rows=34) (actual time=0.012..0.031 rows=11 loops=1)
EXPLAIN ANALYZE
SELECT COUNT(rn) OVER (PARTITION BY rn = 1) AS "Unique count"
FROM
(
SELECT
t_id, status_to,
ROW_NUMBER() OVER (PARTITION BY status_to ORDER BY status_to) AS rn
FROM test
WHERE status_to IS NOT NULL
ORDER BY status_to, t_id
) AS tab
GROUP BY status_to, rn
ORDER BY status_to
LIMIT 1;
EXPLAIN
-> Limit: 1 row(s) (actual time=0.290..0.290 rows=1 loops=1)
    -> Sort: tab.status_to (actual time=0.289..0.289 rows=1 loops=1)
        -> Table scan on <temporary> (cost=2.50..2.50 rows=0) (actual time=0.268..0.272 rows=30 loops=1)
            -> Temporary table (cost=2.50..2.50 rows=0) (actual time=0.267..0.267 rows=30 loops=1)
                -> Window aggregate with buffering: count(rn) OVER (PARTITION BY (tab.rn = 1) ) (actual time=0.188..0.246 rows=30 loops=1)
                    -> Sort: `(tab.rn = 1)` (actual time=0.145..0.148 rows=30 loops=1)
                        -> Table scan on <temporary> (cost=2.50..2.50 rows=0) (actual time=0.128..0.132 rows=30 loops=1)
                            -> Temporary table with deduplication (cost=5.00..5.00 rows=0) (actual time=0.127..0.127 rows=30 loops=1)
                                -> Table scan on tab (cost=2.50..2.50 rows=0) (actual time=0.102..0.107 rows=30 loops=1)
                                    -> Materialize (cost=2.50..2.50 rows=0) (actual time=0.102..0.102 rows=30 loops=1)
                                        -> Sort: test.status_to, test.t_id (actual time=0.091..0.093 rows=30 loops=1)
                                            -> Table scan on <temporary> (cost=2.50..2.50 rows=0) (actual time=0.073..0.077 rows=30 loops=1)
                                                -> Temporary table (cost=2.50..2.50 rows=0) (actual time=0.072..0.072 rows=30 loops=1)
                                                    -> Window aggregate: row_number() OVER (PARTITION BY test.status_to ORDER BY test.status_to ) (actual time=0.053..0.066 rows=30 loops=1)
                                                        -> Sort: test.status_to, test.status_to (cost=6.44 rows=30) (actual time=0.050..0.052 rows=30 loops=1)
                                                            -> Filter: (test.status_to is not null) (cost=6.44 rows=30) (actual time=0.015..0.032 rows=30 loops=1)
                                                                -> Index range scan on test using st_ix over (NULL < status_to) (cost=6.44 rows=30) (actual time=0.012..0.026 rows=30 loops=1)