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.

You can get the result into a variable and use the solution from this question: https://stackoverflow.com/questions/175962/dynamic-select-top-var-in-sql-server — iceblade, Mar 29 '22 at 20:35
This looks wrong. Do you mean `SELECT COUNT(DISTINCT Records)` instead of `SELECT DISTINCT Records` maybe? `Records` is a column name, yes? — Thorsten Kettner, Mar 29 '22 at 21:09
And your explanation could be much better. "I have a table where I have a specific, constant value" - A table with a constant value? How is that? Does the table only have one row or what are you talking about? "let's say it's 15 (column defined as float)" - well, 15 is an integer, and why would you use float in a database? It's an approximate data type and your 15 may become 14.00000001. Where is that 15 in your query? I see the string '33'. Why is it a string? You say there is a result, e.g. 13. Still no 15. Where is it? ... — Thorsten Kettner, Mar 29 '22 at 21:11
"In the same table I have one column where sometimes there is a value ..." What is the name of that column? Where is it in your query? Your request would benefit from you showing sample data and the expected result. — Thorsten Kettner, Mar 29 '22 at 21:13

score 0 · Accepted Answer · answered Mar 29 '22 at 21:25

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;

Using subtraction of two queries in SELECT TOP query

1 Answers1