CODEWITHSUNDEEP
sqlsql-serverselect
Ashish Karnavat
Ashish Karnavat

Reputation: 96

How to get 50% records from a table in SQL Server?

Suppose I have a table with 1000 rows and I want 50% of it in the output. How can I do that? Does it have any in-built function?

Upvotes: 3

Views: 16471

Answers (6)

Varun Sharma
Varun Sharma

Reputation: 1

Create  Table   Employee(
                EmpID int NOT NULL,
                EmpName Varchar (50),
                Gender Char (50), 
                Salary int,
                City Char(50)
                )



Insert Into Employee Values (1, 'Arjun',    'M',    75000,  'Pune'),
                            (2, 'Ekadanta', 'M',    125000, 'Bangalore'),
                            (3, 'Lalita',   'F',    150000, 'Mathura'),
                            (4, 'Madhav',   'M',    250000, 'Delhi'),
                            (5, 'Visakha',  'F',    120000, 'Mathura')
                            (6, 'Vedaant',  'F',    80000,  'Gurugram')

Select* From       Employee
Where              EmpID <= (Select Count(EmpID)/2  From    Employee)

Upvotes: 0

Monirrad
Monirrad

Reputation: 483

SELECT * FROM table 

LIMIT (SELECT COUNT(*)/2 FROM table)

Upvotes: -1

Paul Maxwell
Paul Maxwell

Reputation: 35563

In TSQL you can use TOP n PERCENT but you should also order the output so that the "percentage of" is also specified, otherwise the result is indeterminate. By way of a simple example if rows are unordered (in this case the first insert is 6 not 1):

CREATE TABLE mytable (id INT)

INSERT INTO mytable (id)
VALUES
      (6)
    , (7)
    , (8)
    , (9)
    , (10)
    , (1)
    , (2)
    , (3)
    , (4)
    , (5) ;

This, if we simply ask for top 50 percent the output is

select top 50 percent
 id
from mytable

| id |
|  6 |
|  7 |
|  8 |
|  9 |
| 10 |

But if we use an order by clause then the result is more meaningful.

select top 50 percent
 id
from mytable
order by id

| id |
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |

It was also asked if a similar result could be determined using row_number(), so here is a method

select
 id
from (
    select
       id
     , count(*) over(partition by (select 1)) all_count
     , row_number() over(order by id)         rn
    from mytable
    ) d
where rn <= all_count / 2

| id |
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |

db<>fiddle here

Upvotes: 1

Sahil Anand
Sahil Anand

Reputation: 139

Here is the solution:

select top 50 percent *
from TableName

Upvotes: 1

Ravi
Ravi

Reputation: 1172

Use :

SELECT 
    TOP 50 PERCENT * 
FROM 
    Table1;

with Row_number

SELECT 
        TOP 50 PERCENT Row_Number() over (order by Column1) ,* 
    FROM 
        Table1;

Note: Row_number should have a over clause with order by column or partition by columns

Upvotes: 6

Mureinik
Mureinik

Reputation: 311163

The top syntax supports a percent modifier, which you can use:

SELECT TOP 50 PERCENT *
FROM   mytable

Upvotes: 3

Related Questions