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)
);
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');
Records: 31 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 | 2 |
| 13 | Action | 4 |
| 18 | Action | 1 |
| 23 | Action | 3 |
| 1 | Draft | 2 |
| 10 | Draft | 4 |
| 26 | Draft | 1 |
| 27 | Draft | 3 |
| 6 | Let | 3 |
| 15 | Let | 2 |
| 20 | Let | 1 |
| 25 | Let | 4 |
| 3 | Publish | 4 |
| 12 | Publish | 1 |
| 17 | Publish | 3 |
| 22 | Publish | 2 |
| 5 | Sold | 4 |
| 14 | Sold | 2 |
| 19 | Sold | 3 |
| 24 | Sold | 1 |
| 8 | Test_val_1 | 1 |
| 35 | Test_val_1 | 2 |
| 9 | Test_val_2 | 2 |
| 31 | Test_val_2 | 1 |
| 2 | Unpublish | 4 |
| 11 | Unpublish | 3 |
| 16 | Unpublish | 1 |
| 21 | Unpublish | 2 |
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
ORDER BY status_to
LIMIT 1;
| Unique count |
|---|
| 7 |
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
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 |