Splitting Result Of Select Statement into Two Equal Half's In SQL Server

In this article, I will explain how to split a table in two equal half's.
  • 4382

In this article, I will explain how to split a table in two equal half's.

Create Table

CREATE TABLE [dbo].[Student1Details](

      [ID] [int] NOT NULL,

      [Name] [varchar](50) NULL,

      [Branch] [varchar](10) NULL,

      [Location] [varchar](10) NULL,

 CONSTRAINT [PK_Student1] PRIMARY KEY CLUSTERED

(

      [ID] ASC

)

) ON [PRIMARY]

Insert some values

INSERT INTO Student1Details

SELECT 1, 'Nitin', 'CS','IND' UNION ALL

SELECT 2, 'Ravi', 'EI','ENG' UNION ALL

SELECT 3, 'Tim', 'ME','US' UNION ALL

SELECT 4, 'Rick', 'ME','IND' UNION ALL

SELECT 5, 'Rakesh', 'CS','ABD' UNION ALL

SELECT 6, 'Tarun', 'ME','IND' UNION ALL

SELECT 7,'Raushan','IT','IND' UNION ALL

SELECT 8,'Aman','EC','US'

SELECT * FROM Student1Details

Student1Details Table

StudentTable.jpg

Splitting Student1Details into two half's

DECLARE @CountOf int,@Top int,@Bottom int

SELECT @CountOf=COUNT(*) FROM [Student1Details]

SET @Top=@CountOf/2

SET @Bottom=@CountOf-@Top

SELECT TOP (@Top) * FROM [Student1Details] ORDER BY 1 asc

SELECT TOP (@Bottom) * FROM [Student1Details] ORDER BY 1 desc

Output:

half.jpg

© 2013 dotNetheaven. All rights reserved.