CODEWITHSUNDEEP
sqlsql-servert-sqlsql-order-by
Lana B
Lana B

Reputation: 496

Selecting a number of rows with TOP based on expression

I am trying to break the table into 2 parts. (SQL Server 2017) this is my code: 

 SELECT TOP COUNT(*)/2 TargetColumn
 FROM SomeTable

The error message is:

Incorrect syntax near 'COUNT'

TOP doesn't allow me to do that. expressions using fixed numbers aka 2000/2 works, but the above one using COUNT does not.

I have searched StackOverflow but have found only this post that is relevant, which has 2 answers, one of which was branded "dangerous", and the second one I did not understand.

Upvotes: 0

Views: 88

Answers (2)

Dale K
Dale K

Reputation: 27202

You can't do it in a single query, you need to get the rowcount and then use that for your top query e.g.

DECLARE @NumRows INT;

SELECT @NumRows = COUNT(*)/2
FROM SomeTable;

SELECT TOP(@NumRows) TargetColumn
FROM SomeTable;

This is because TOP is a simple function designed to limit the number of rows returned. Having to count the rows requires all rows to be returned which is a contradiction.

If you wish to perform this operation in a single query please see @GMB's answer.

For the table I tested on, this solution took 28% and GMB's took 72% without an order by. If you add an order by this solution took 52% and GMB's took 48%. So the best performing query depends on your specific scenario.

Upvotes: 1

GMB
GMB

Reputation: 222432

You can do this in a single query using window functions:

select targetcolumn
from (
    select 
        targetcolumn, 
        row_number() over(order by somecol) rn,
        count(*) over() cnt
    from sometable
) t
where rn <= cnt / 2

The subquery performs a window count() of all records in the table, and ranks records with row_number(). Then, the outer query filters on the bottom half.

Note that using TOP without an ORDER BY clause does not really make sense: the ordering of the records is inherently undefined unless explicitly specified, so this results in an undefined set of rows being returned (although with a fixed number of records). I assumed that you have a column for ordering, and called it somecol in the above query.

Upvotes: 1

Related Questions