T-SQL: how to get 1/2 of the total number of rows (by some criteria) or the first 50 rows?

Question

I am wondering how in T-SQL I can get - let's say - 1/2 of the total number of rows from a table (based on some criteria) or even the first 50 rows (based on some criteria too)?

Answer 1

using SQL TOP clause

SQL Server Syntax

 SELECT TOP number|percent column_name(s)
   FROM table_name

 example : 

   SELECT TOP 50 PERCENT * FROM tablename

MySQL syntax

SELECT column_name(s)
FROM table_name
LIMIT number

Oracle Syntax

SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number

Answer 2

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 ORDER BY if you want consistent results.

Answer 3

-:

SELECT TOP 50 PERCENT a, b, c FROM table

-:

SELECT TOP 50 a, b, c FROM table

, , ORDER BY ( , , ORDER BY, , ).

Paging (for example, returning a block of x rows of y) is more cumbersome in SQLServer than many other SQL relational databases (more cumbersome than all, 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

Selects a third set of ten rows, ordered by column d.

This last method is also necessary when the limit comes from a variable, because TOP does not allow something like TOP @number.

Answer 4

The following works in MS SQL 2005 and 2008

select top 50 PERCENT * from x