By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tbl_fileStatus (
`f_id` INTEGER,
`f_bankid` INTEGER,
`f_filestatus` INTEGER
);
INSERT INTO tbl_fileStatus
(`f_id`, `f_bankid`, `f_filestatus`)
VALUES
('1', '1', '1'),
('2', '2', '1'),
('3', '2', '2'),
('4', '1', '2'),
('5', '1', '3'),
('6', '3', '2'),
('7', '3', '3');
SELECT COUNT(*) AS tcount
FROM (
SELECT DISTINCT f_bankid,
FIRST_VALUE(f_filestatus) OVER (PARTITION BY f_bankid ORDER BY f_id DESC) f_filestatus
FROM tbl_fileStatus
) t
WHERE f_filestatus = 1
| tcount |
|---|
| 0 |
SELECT COUNT(*) AS tcount
FROM (
SELECT DISTINCT f_bankid,
FIRST_VALUE(f_filestatus) OVER (PARTITION BY f_bankid ORDER BY f_id DESC) f_filestatus
FROM tbl_fileStatus
) t
WHERE f_filestatus = 2
| tcount |
|---|
| 1 |
SELECT COUNT(*) AS tcount
FROM (
SELECT DISTINCT f_bankid,
FIRST_VALUE(f_filestatus) OVER (PARTITION BY f_bankid ORDER BY f_id DESC) f_filestatus
FROM tbl_fileStatus
) t
WHERE f_filestatus = 3
| tcount |
|---|
| 2 |