asked on

Spliting result sql statement

Hi,

I need to find a way to split a select in 2 or more grouping in a sql statement.

For exemple, I have one select with 1000 lines and i need to split those lines in 3. The first 500 (1 to 500) and then 250 (501 to 750) and the last 250 (751 to 1000). I need that in a sql statement.

It's TSQL and I'm using microsoft sql server, a prior versions, not the 2005

Any thoughts on how to do this?

Thanks

Aneesh

declare @Tab table (i int identity, <+ other columns the select statement returns > )
insert into @Tab (<ColList other than identity column>
SELECT <statement>

SELECT * from @Tab where tab i <500

R_Hos

you can also use the SELECT TOP ( # of rows) for the first query

and then use:
SELECT *
FROM [your table name]
WHERE [primary key field] not in (SELECT TOP (# of rows) [primary key field] FROM [your table name])

~R

thughesWORK

try

/*
drop table foo
create table foo (i1 int)
declare @loop int
select @loop = 1000
while (@loop <= 2000)
BEGIN
insert into foo values (@loop)
select @loop = @loop + 1
END
*/

declare @seq table (pk int identity, i1 char(10))

insert into @seq select i1 from foo

select case
when pk between 1 and 500 then 'Group1'
when pk between 501 and 750 then 'Group2'
when pk between 751 and 1000 then 'Group3'
end as key_group,
count(1)
from @seq
group by
case
when pk between 1 and 500 then 'Group1'
when pk between 501 and 750 then 'Group2'
when pk between 751 and 1000 then 'Group3'
end

ASKER CERTIFIED SOLUTION

lyhsonhnvn

membership

This solution is only available to members.

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

Start Free Trial

MetDia

ASKER

By splitting, I mean creating 3 tables out of the first Main table that is already sorted by a specific column...

A way I found to do it is:

* Pick the top 500: SELECT TOP 500 FROM myTable ORDER BY column ASC
* Pick the next 250: SELECT TOP 250 FROM (
SELECT TOP 750 FROM myTable ORDER BY column DESC
) ORDER BY colomun ASC
* Pick the next 250: SELECT TOP 250 FROM (
SELECT TOP 1000 FROM myTable ORDER BY column DESC
) ORDER BY colomun ASC

MetDia

ASKER

Another way is:

* Pick the top 500: SELECT TOP 500 FROM myTable ORDER BY column ASC

* Pick the next 250: SELECT TOP 250 FROM myTable
WHERE column NOT IN(
SELECT TOP 500 column FROM myTable ORDER BY column ASC
) ORDER BY column ASC

* Pick the next 250: SELECT TOP 250 FROM myTable
WHERE column NOT IN(
SELECT TOP 750 column FROM myTable ORDER BY column ASC
) ORDER BY column ASC