• Status: Solved
• Priority: Medium
• Security: Public
• Views: 581

# Select half of the records in a table

I am trying to write a stored proc that will return the first half or the second half of the records in a table based on a parameter. any Ideas?
0
JonMny
1 Solution

Commented:
Create Procedure TempProc(@Val as Varchar(50))

Declare @TotalCount as Int
Declare @SQL as Varchar(100)

Select @TotalCount = Count(*) from YourTable

IF @Val = 'Top'
Set @SQL = 'Select Top ' + Cast(@TotalCount/2 as Varchar(5)) + ' from YourTable order by UniqueId'
else
Set @SQL = 'Select Top ' + Cast(@TotalCount/2 as Varchar(5)) + ' from YourTable order by UniqueId desc'

Exec(@SQL)

Return
0

Commented:
in case you dont have a UniqueID column in your table use this approach

Declare @Rows int
Select       IDENTITY(int, 1,1) AS RowID,
*
Into       #T
From      tBAKFileStat_Current
Select @Rows=@@RowCount/2

Select * from #T Where RowID <=@Rows
Select * from #T Where RowID >@Rows
0

Commented:
oops I was testing it with my tBAKFileStat_Current table... you can replace tBAKFileStat_Current with your table name

0

Commented:
delcare @cnt int, @stmt varchar(4000)
select @cnt = count(*) from yourtablename

--for first half
set @stmt = 'select top ' + cast( @cnt/2  as varchar(10))+' from yourtablename order by pk asc'
--for second half
set @stmt = 'select top ' + cast( @cnt/2  as varchar(10))+' from yourtablename order by pk desc'
exec @stmt
0

Commented:
try "SELECT TOP 50 PERCENT"

SELECT TOP 50 PERCENT * from Northwind.dbo.Categories

more

http://msdn2.microsoft.com/en-us/library/ms189463.aspx
0

Commented:
what if i've odd number of records :).
declare @count int
select @count = count(pid) from table
if @count % 2 = 0
begin  //imran sorry i just copied your answer as i think it will be better that PERCNTAGE
--for first half
set @stmt = 'select top ' + cast( @cnt/2  as varchar(10))+' from yourtablename order by pk asc'
--for second half
set @stmt = 'select top ' + cast( @cnt/2  as varchar(10))+' from yourtablename order by pk desc'
exec @stmt
end
else if @Count % 2 > 0
begin
--for first half
set @stmt = 'select top ' + cast( @cnt-1/2  as varchar(10))+' from yourtablename order by pk asc'
--for second half
set @stmt = 'select top ' + cast( (@cnt/2)+1  as varchar(10))+' from yourtablename order by pk desc'
exec @stmt

i guess it should help

regards,
satish.
end
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.