Solved

# Select half of the records in a table

Posted on 2006-11-17
550 Views
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
Question by:JonMny
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 9

Accepted Solution

dduser earned 500 total points
ID: 17966685
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

LVL 11

Expert Comment

ID: 17967330
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

LVL 11

Expert Comment

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

0

LVL 28

Expert Comment

ID: 17971421
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

LVL 43

Expert Comment

ID: 17973451
try "SELECT TOP 50 PERCENT"

SELECT TOP 50 PERCENT * from Northwind.dbo.Categories

more

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

LVL 4

Expert Comment

ID: 17977467
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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
###### Suggested Courses
Course of the Month5 days, 14 hours left to enroll