Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Select half of the records in a table

Posted on 2006-11-17
6
Medium Priority
?
569 Views
Last Modified: 2008-02-01
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
Comment
Question by:JonMny
6 Comments
 
LVL 9

Accepted Solution

by:
dduser earned 2000 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

by:rw3admin
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

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

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 28

Expert Comment

by:imran_fast
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

by:Eugene Z
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

by:satish_nagdev
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question