Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Query to select top 10 then next 15

Author  Topic 

JeffT
Posting Yak Master

111 Posts

Posted - 2007-01-30 : 08:42:03
Hi,
Running a query via BCP from a batch file, I'm trying to select the top 10 rows from a table, output them to a file, and then select the NEXT 15 and output those to another file. I googled an EXCEPT command and tried to use it but got an "incorrect syntax" message. Is there another way I can do this ?
Thanks,
Jeff

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-30 : 08:45:10
insert into #temp ( . . .)
select top 25 . . .
from table
order by somecol

-- top 10
select top 10 . . .
from #temp
order by somecol

-- top 15
select top 15 . .
from #temp
order by somecol desc
order by somecol


KH

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-30 : 08:47:19
INSERT the data you want to export into a temporary table with an IDENTITY(0, 1) column named RowID.

Now you can export the first 10 records with
SELECT * FROM #Temp WHERE RowID < 10

Then you can export the next 15 records with
SELECT Col1, Col2, ColX FROM #Temp WHERE RowID % 5 IN (3, 4, 5)

Then you can export the next 15 records with
SELECT Col1, Col2, ColX FROM #Temp WHERE RowID % 5 IN (6, 7, 8)


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

JeffT
Posting Yak Master

111 Posts

Posted - 2007-01-30 : 08:47:49
Thanks khtan ! I will try this.
J
Go to Top of Page

JeffT
Posting Yak Master

111 Posts

Posted - 2007-01-30 : 13:42:31
Thanks Peter !
Could you explain a little more about what your second SELECT is doing ?
SELECT Col1, Col2, ColX FROM #Temp WHERE RowID % 5 IN (3, 4, 5)
I don't understand the "WHERE RowID % 5 IN (3, 4, 5)" part of it.
Thanks,
J
Go to Top of Page

JeffT
Posting Yak Master

111 Posts

Posted - 2007-01-30 : 14:04:08
Peter,
I created that temp table with the IDENTITY and tried to insert the data but got this message:

An explicit value for the identity column in table '#temp_create_batch_jeff' can only be specified when a column list is used and IDENTITY_INSERT is ON.
This is the first two lines of the create table:

CREATE TABLE [#temp_create_batch_jeff] (
[RowID] [int] IDENTITY (0, 1) NOT NULL ,

Thanks,
J

Go to Top of Page

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-01-30 : 14:30:42
You must not insert a value into the RowID column yourself because it is an identity (auto incrementing number) column. Here is an example selecting rows from the Northwind Customers table (note that the select does not have a column for the RowID).
CREATE TABLE [#temp_create_batch_jeff] (
[RowID] [int] IDENTITY NOT NULL ,
CompanyName varchar(50))

INSERT #temp_create_batch_jeff (CompanyName)
SELECT CompanyName FROM Northwind..Customers ORDER BY CompanyName

SELECT * FROM #temp_create_batch_jeff WHERE RowID <= 10
SELECT * FROM #temp_create_batch_jeff WHERE RowID BETWEEN 11 AND 25
SELECT * FROM #temp_create_batch_jeff WHERE RowID BETWEEN 26 AND 40

DROP TABLE [#temp_create_batch_jeff]

Peter's code is using the modulus operator (%) to test RowIds by dividing by 5 and taking the remainder, but I think using a simple BETWEEN as in my example is as fast and simpler in your case.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-30 : 14:34:53
My typo...
% should be /


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-30 : 14:42:33
INSERT the data you want to export into a temporary table with an IDENTITY(5, 1) column named RowID.

SELECT Col1, Col2, ColX, IDENTITY(INT, 5, 1) AS RowID INTO #Temp FROM YourOriginalTableNameHere
ORDER BY ....

1)
Now you can export the first 10 records with
SELECT Col1, Col2, ColX FROM #Temp WHERE RowID / 15 = 0

2)
Then you can export the next 15 records with
SELECT Col1, Col2, ColX FROM #Temp WHERE RowID / 15 = 1 --<-- filenumber

3)
Then you can export the next 15 records with
SELECT Col1, Col2, ColX FROM #Temp WHERE RowID / 15 = 2 --<-- filenumber

4)
And the next 15 records with
SELECT Col1, Col2, ColX FROM #Temp WHERE RowID / 15 = 3 --<-- filenumber

With this technique, it will be very easy with a WHILE to create new filenames for every loop and export data according to "loop counter".


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-30 : 15:30:46
Or, if you are having trouble with zero-based counters:

INSERT the data you want to export into a temporary table with an IDENTITY(20, 1) column named RowID.

SELECT Col1, Col2, ColX, IDENTITY(INT, 20, 1) AS RowID INTO #Temp FROM YourOriginalTableNameHere
ORDER BY ....

1)
Now you can export the first 10 records with
SELECT Col1, Col2, ColX FROM #Temp WHERE RowID / 15 = 1 --<-- filenumber

2)
Then you can export the next 15 records with
SELECT Col1, Col2, ColX FROM #Temp WHERE RowID / 15 = 2 --<-- filenumber

3)
Then you can export the next 15 records with
SELECT Col1, Col2, ColX FROM #Temp WHERE RowID / 15 = 3 --<-- filenumber

4)
And the next 15 records with
SELECT Col1, Col2, ColX FROM #Temp WHERE RowID / 15 = 4 --<-- filenumber

With this technique, it will be very easy with a WHILE to create new filenames for every loop and export data according to "loop counter".


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

JeffT
Posting Yak Master

111 Posts

Posted - 2007-01-31 : 13:59:53
Thanks very much Peter !
J
Go to Top of Page
   

- Advertisement -