SQL Count query. Even numbers odd numbers

Question

I have a product table, I need to know the number of records in the table. The Goal is to divide the return query into 2 separate query.

If (Count(*) % 2) = 0 return top(Count(*) / 2)
else return top((Count(*) / 2) + 1)

The main query is :

select coalesce(Price, ProductPrice) as Price, Product.ProductName, Customer.CustomerName, Product.CatalogNum from Product 
        inner join Customer on CustomerID = @custId
        left outer join CustomerPrice on dbo.Customer.CustomerID = dbo.CustomerPrice.CustomerID 
        and dbo.Product.ProductID = dbo.CustomerPrice.ProductID
        Where Product.ProductActive = 1 Order by Product.CatalogNum

Answer 1

DECLARE @Count int
SELECT @Count = COUNT(*) FROM SomeTable
SET @Count = CASE WHEN @Count % 2 = 0 THEN @Count / 2 ELSE @Count / 2 + 1 END
SELECT TOP(@Count) * FROM SomeTable ORDER BY SomeColumn

Note that there is a concurrency issue here - the number of rows might change between the first and second select statements, unless you apply a suitably restrictive locking hint/transaction isolation level.

Note also that an ORDER BY is essential to give any meaning to "top half".

For information on the TOP clause.

To get the bottom half do the same but subtract one if odd count and reverse the sort order a couple of times.

DECLARE @Count int
SELECT @Count = COUNT(*) FROM SomeTable
SET @Count = CASE WHEN @Count % 2 = 0 THEN @Count / 2 ELSE @Count / 2 - 1 END
SELECT * FROM (
    SELECT TOP(@Count) * FROM SomeTable ORDER BY SomeColumn DESC) AS Data
ORDER BY SomeColumn