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

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
Asked:
JonMny
1 Solution
 
dduserCommented:
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
 
rw3adminCommented:
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
 
rw3adminCommented:
oops I was testing it with my tBAKFileStat_Current table... you can replace tBAKFileStat_Current with your table name

0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
imran_fastCommented:
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
 
Eugene ZCommented:
try "SELECT TOP 50 PERCENT"



SELECT TOP 50 PERCENT * from Northwind.dbo.Categories

more

http://msdn2.microsoft.com/en-us/library/ms189463.aspx
0
 
satish_nagdevCommented:
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.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now