Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Select half of the records in a table

Posted on 2006-11-17
6
Medium Priority
?
555 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
[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
  • Learn & ask questions
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
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
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

715 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