T-SQL: How to retrieve a 1/2 of total row count (basing on some criteria) or 50 first rows?

Question

I wondering about how in T-SQL can I retrieve - let's say - 1/2 of total row count form a table (basing on some criteria) or even the first 50 rows (basing on some criteria too) ?

Answer 1

To select the top 50 rows:

SELECT TOP 50 *
FROM table1
WHERE ...
ORDER BY ...

To select the first half of the result set use PERCENT:

SELECT TOP 50 PERCENT *
FROM table1
WHERE ...
ORDER BY ...

Remember to add an ORDER BY if you want the results to be consistent.

Answer 2

First:

SELECT TOP 50 PERCENT a, b, c FROM table

Second:

SELECT TOP 50 a, b, c FROM table

As a rule, it's not advisable to do this unless you are also providing an ORDER BY (and in those times where it will work perfectly well to leave out the ORDER BY, your code will be more understandable and more robust to changes in the details of the underlying tables if you put it in).

Paging (e.g. returning the x block of y rows) is more cumbersome in SQLServer than many other SQL-language relational databases (more cumbersome than just about all of them to be honest), but can be done with ROW_NUMBER:

WITH OrderedTable AS
(
  SELECT a, b, c, ROW_NUMBER() OVER (ORDER BY d) as rowNumber
  FROM table
)
SELECT a, b, c FROM OrderedTable
WHERE rowNumber between 31 and 40

Will select the third set of ten rows, ordered by column d.

This latter method is also needed when the limit comes from a variable, as TOP does not allow something like TOP @number.