I have a table of records (all_records) that contains a recordID and status (open/closed). For example,

ID Status

1 Closed

2 Closed

3 Closed

4 Closed

5 Open

I am trying to write a query to return the count of closed records of the 3 records with the highest ID. So, with the above, it would return 2 since of the 3 records with the highest ID, two are closed. I thought the below would work, but it is returning 4 not 2:

select 
  recordid,
  (select top 3 count(recordid) from all_records where status = 'Closed') as CNT 
from 
  all_records 

CodePudding user response：

How about conditional sum? A CTE (you could use a subquery instead, if you want) "sorts" rows by ID in descending order - it (rn) is then used in the final where clause so that only the last 3 IDs are being used.

SQL> with temp as
  2    (select id, status,
  3      row_number() over (order by id desc) rn
  4     from test
  5    )
  6  select sum(case when status = 'Closed' then 1 else 0 end) as cnt
  7  from temp
  8  where rn <= 3;

       CNT
----------
         2

SQL>

CodePudding user response：

This will probably work in any ANSI SQL Database (SELECT/INSERT but not CREATE)

create table tu (id int, name varchar(20));

insert into tu values (1, 'Closed');
insert into tu values (2, 'Closed');
insert into tu values (3, 'Closed');
insert into tu values (4, 'Closed');
insert into tu values (5, 'Open');
insert into tu values (6, 'Open');

select max(id) maxId, count(name) count, name
from tu
group by name;

Result