asked on

splitting result set

hi guys

I have 1,200,000 rows in my table customer

Table Customer has 4 columns
Id
Name
City
dept

Is it possible to say

select 'first quarter of the rows' from Customer - return first 300000 rows
Select 'second quarter of the rows' from Customer - return the next 300000 rows
Select 'third quarter of the rows' from Customer - return the next 300000 rows
Select 'fourth quarter of the rows' from Customer - return the next 300000 rows

Any idea how i can go about doing that.

Thanks.

Kent Olsen

Is the ID column an IDENTITY and nearly sequential?

If so, just limit the query based on that.

SELECT * FROM mytable WHERE id < 300000;
SELECT * FROM mytable WHERE id between 300001 AND 600000;
SELECT * FROM mytable WHERE id between 600001 AND 900000;
SELECT * FROM mytable WHERE id > 900000;

Kent

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jay Roy

ASKER

thanks kent..
Actually no, the id is like unique id, non-sequential.
id : A123
B657
D787
ect

any other approach ?
thanks.

SOLUTION

Kent Olsen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jay Roy

ASKER

thanks guys

>>INSERT INTO #tmp (id, name, city, dept, quartile,
SELECT Id, Name, City, dept, NTILE(4) OVER (ORDER BY id)
FROM YourTable

Does the id have to be unique ? What happens if id is not unique?
thanks.

Kent Olsen

Hi Roy,

That uses the same structure as your original table. The NTILE function splits the data into fourths based on the number of rows in the table.