Using subtraction of two queries in SELECT TOP query

Question

I am struggling to prepare a query like this in SQL Server:

• I have a table where I have a specific, constant value, let's say it's 15 (column defined as float)
• In the same table I have one column where sometimes there is a value and sometimes it is a NULL value

So I would like to use SELECT TOP () query that would show me the number of records that is a result of subtraction of two queries:

SELECT 
    (SELECT DISTINCT Records 
     FROM Brand.Alle
     WHERE HdNummer = '33')
    -
    (SELECT COUNT(AbrufNr) 
     FROM Brand.Alle
     WHERE HdNummer = '33' 
       AND Transaction IS NOT NULL) AS DIFFERENCE

This query returns the result I want to have (let's say 13).

I would like to have selected top 13 records from a table I run a query against:

SELECT TOP (SELECT 
                (SELECT DISTINCT Records FROM Brand.Alle
                 WHERE HdNummer = '33')
                -
                (SELECT COUNT(AbrufNr) FROM Brand.Alle
                 WHERE HdNummer = '33' AND Transaction IS NOT NULL) AS DIFFERENCE) * 
FROM Brand.Alle
WHERE HdNummer = '33' AND Transaction IS NULL
ORDER BY NEWID()

but it fails due to an error saying that I need to use an integer in select top statement. So the question is: how can I convert the value I receive as a result of subtraction two queries so I could use in in SELECT TOP?

I would highly appreciate any help.

Thank you in advance.

Answer 1

Number all your rows and only keep those with a number less or equal to the desired count.

with numbered as
(
  select
    alle.*,
    row_number() over (order by newid()) as rn
  from brand.alle
  where hdnummer = 33
)
select * 
from numbered
where rn <= ( <your count query here> );

Your count query can probably written shorter along the lines of:

select 
  count(distinct records) - 
  count(case when transaction is not null then abrufnr end) as cnt
from brand.alle
where hdnummer = 33;

And you can even combine the two with window functions in order to read from the table only once.

with numbered_and_counted as
(
  select 
    alle.*,
    count(distinct records) over () - 
    count(case when transaction is not null then abrufnr end) over () as cnt,
    row_number() over (order by newid()) as rn
  from brand.alle
  where hdnummer = 33
) 
select *
from numbered_and_counted
where rn <= cnt;