numbering records returned in a query

[tag]

Using SQLServer 7, In addition to returning a group of records from a pretty
basic select statement, I have a need to insert record numbers to each row.
The numbers are to be calculated 1-X based on the results of the query (not
their position in the table). In other words, there are a total of 4 fields
needed for a text output file (zip, lat, lon, and recnum). The zip, lat,
and lon fields reside in one table, however the recnum field needs to be
added so that each row is numbered sequentially

zip lat lon RECNUM

01501, 084, 180,1
99925, 033, 223,2
etc..

I tried declaring a cursor and looping through the records but I must be
be doing something wrong. I am hoping to:

1) Use DTS to run the select statement to grab the records that I am looking
for
2) Add a recnum to the record set
3) Output the results to a delimited text file.

I have the whole thing working except for the record counting. I would appreciate
any help.

Paul

[PP]

A simple hack could be:
1) Create a working table that has an identity field
2) Insert the data into the working table using the order by clause of the
select statement to force the correct ordering.

Share & Enjoy
PP


"tag" <taglia@mediaone.net> wrote:
>
>Using SQLServer 7, In addition to returning a group of records from a pretty
>basic select statement, I have a need to insert record numbers to each row.
> The numbers are to be calculated 1-X based on the results of the query
(not
>their position in the table). In other words, there are a total of 4 fields
>needed for a text output file (zip, lat, lon, and recnum). The zip, lat,
>and lon fields reside in one table, however the recnum field needs to be
>added so that each row is numbered sequentially
>
>zip lat lon RECNUM
>
>01501, 084, 180,1
>99925, 033, 223,2
>etc..
>
>I tried declaring a cursor and looping through the records but I must be
>be doing something wrong. I am hoping to:
>
>1) Use DTS to run the select statement to grab the records that I am looking
>for
>2) Add a recnum to the record set
>3) Output the results to a delimited text file.
>
>I have the whole thing working except for the record counting. I would
appreciate
>any help.
>
>Paul
>

[Mokgobi]

"tag" <taglia@mediaone.net> wrote:
>
>Using SQLServer 7, In addition to returning a group of records from a pretty
>basic select statement, I have a need to insert record numbers to each row.
> The numbers are to be calculated 1-X based on the results of the query
(not
>their position in the table). In other words, there are a total of 4 fields
>needed for a text output file (zip, lat, lon, and recnum). The zip, lat,
>and lon fields reside in one table, however the recnum field needs to be
>added so that each row is numbered sequentially
>
>zip lat lon RECNUM
>
>01501, 084, 180,1
>99925, 033, 223,2
>etc..
>
>I tried declaring a cursor and looping through the records but I must be
>be doing something wrong. I am hoping to:
>
>1) Use DTS to run the select statement to grab the records that I am looking
>for
>2) Add a recnum to the record set
>3) Output the results to a delimited text file.
>
>I have the whole thing working except for the record counting. I would
appreciate
>any help.
>
>Paul
>

I am working on ORACLE DB , but I think you should be able to use
ROWNUM to retrieve the record number

Try
Select zip,lat,long,ROWNUM into
:zip,:lat,:long,record_num
from table
where <condition>

Select zip,lat,long,ROWNUM is what you are looking for , I guess
Hope it helps. Mokgobi , Pretoria South Africa.

[Paul]

Thanks for your recommendation. I did create a new table, set an ID field,
set the seed value to 1 and increment to 1 also. This method comes very
close to the perfect solution. The only issue is that the id field continues
to increment the number even though I delete all rows before writing to the
table.

Without getting too detailed, the main table holds the GPS position of a
fleet of 20 tractor trailers. Since I pick the data up every 15 minutes,
the main table has thousands of rows with timestamped position data. The
select statement that I run simply reports the most recent sighting of the
20 trucks - i.e. the query returns one record for each truck. Since I am
using this new table to send to a mapping application, I clear the new table,
then update the table with the latest position data. Again, the only issue
is that the counter does not reset by emptying out the table.

Is there any way to have a record counting field in the latest position table
so that it resets to 1 each time I write to it?

Thanks for any ideas...

"PP" <PunkPuffin@Tesco.Net> wrote:
>
>A simple hack could be:
>1) Create a working table that has an identity field
>2) Insert the data into the working table using the order by clause of the
>select statement to force the correct ordering.
>
>Share & Enjoy
>PP
>
>
>"tag" <taglia@mediaone.net> wrote:
>>
>>Using SQLServer 7, In addition to returning a group of records from a pretty
>>basic select statement, I have a need to insert record numbers to each
row.
>> The numbers are to be calculated 1-X based on the results of the query
>(not
>>their position in the table). In other words, there are a total of 4 fields
>>needed for a text output file (zip, lat, lon, and recnum). The zip, lat,
>>and lon fields reside in one table, however the recnum field needs to be
>>added so that each row is numbered sequentially
>>
>>zip lat lon RECNUM
>>
>>01501, 084, 180,1
>>99925, 033, 223,2
>>etc..
>>
>>I tried declaring a cursor and looping through the records but I must be
>>be doing something wrong. I am hoping to:
>>
>>1) Use DTS to run the select statement to grab the records that I am looking
>>for
>>2) Add a recnum to the record set
>>3) Output the results to a delimited text file.
>>
>>I have the whole thing working except for the record counting. I would
>appreciate
>>any help.
>>
>>Paul
>>
>

[PP]

Try using TRUNCATE TABLE (if you are the owner) or select into a temp table
defining one of the columns as IDENTITY(int,1,1) or the DBCC CHECKIDENT which
is for the sysadmin group. Or use an insert trigger instead of the auto incrementing
INDENTITY which might slow things down.

Share & Enjoy



"Paul" <taglia@mediaone.net> wrote:
>
>Thanks for your recommendation. I did create a new table, set an ID field,
>set the seed value to 1 and increment to 1 also. This method comes very
>close to the perfect solution. The only issue is that the id field continues
>to increment the number even though I delete all rows before writing to
the
>table.
>
>Without getting too detailed, the main table holds the GPS position of
a
>fleet of 20 tractor trailers. Since I pick the data up every 15 minutes,
>the main table has thousands of rows with timestamped position data. The
>select statement that I run simply reports the most recent sighting of the
>20 trucks - i.e. the query returns one record for each truck. Since I am
>using this new table to send to a mapping application, I clear the new table,
>then update the table with the latest position data. Again, the only issue
>is that the counter does not reset by emptying out the table.
>
>Is there any way to have a record counting field in the latest position
table
>so that it resets to 1 each time I write to it?
>
>Thanks for any ideas...
>
>"PP" <PunkPuffin@Tesco.Net> wrote:
>>
>>A simple hack could be:
>>1) Create a working table that has an identity field
>>2) Insert the data into the working table using the order by clause of
the
>>select statement to force the correct ordering.
>>
>>Share & Enjoy
>>PP
>>
>>
>>"tag" <taglia@mediaone.net> wrote:
>>>
>>>Using SQLServer 7, In addition to returning a group of records from a
pretty
>>>basic select statement, I have a need to insert record numbers to each
>row.
>>> The numbers are to be calculated 1-X based on the results of the query
>>(not
>>>their position in the table). In other words, there are a total of 4
fields
>>>needed for a text output file (zip, lat, lon, and recnum). The zip, lat,
>>>and lon fields reside in one table, however the recnum field needs to
be
>>>added so that each row is numbered sequentially
>>>
>>>zip lat lon RECNUM
>>>
>>>01501, 084, 180,1
>>>99925, 033, 223,2
>>>etc..
>>>
>>>I tried declaring a cursor and looping through the records but I must
be
>>>be doing something wrong. I am hoping to:
>>>
>>>1) Use DTS to run the select statement to grab the records that I am looking
>>>for
>>>2) Add a recnum to the record set
>>>3) Output the results to a delimited text file.
>>>
>>>I have the whole thing working except for the record counting. I would
>>appreciate
>>>any help.
>>>
>>>Paul
>>>
>>
>

[paul]

THANK YOU MUCH! You were spot on with the TRUNCATE TABLE command. It is
working perfectly now!!!!

Best regards,

Paul


"PP" <PunkPuffin@Tesc.net> wrote:
>
>Try using TRUNCATE TABLE (if you are the owner) or select into a temp table
>defining one of the columns as IDENTITY(int,1,1) or the DBCC CHECKIDENT
which
>is for the sysadmin group. Or use an insert trigger instead of the auto
incrementing
>INDENTITY which might slow things down.
>
>Share & Enjoy
>
>
>
>"Paul" <taglia@mediaone.net> wrote:
>>
>>Thanks for your recommendation. I did create a new table, set an ID field,
>>set the seed value to 1 and increment to 1 also. This method comes very
>>close to the perfect solution. The only issue is that the id field continues
>>to increment the number even though I delete all rows before writing to
>the
>>table.
>>
>>Without getting too detailed, the main table holds the GPS position of
>a
>>fleet of 20 tractor trailers. Since I pick the data up every 15 minutes,
>>the main table has thousands of rows with timestamped position data. The
>>select statement that I run simply reports the most recent sighting of
the
>>20 trucks - i.e. the query returns one record for each truck. Since I
am
>>using this new table to send to a mapping application, I clear the new
table,
>>then update the table with the latest position data. Again, the only issue
>>is that the counter does not reset by emptying out the table.
>>
>>Is there any way to have a record counting field in the latest position
>table
>>so that it resets to 1 each time I write to it?
>>
>>Thanks for any ideas...
>>
>>"PP" <PunkPuffin@Tesco.Net> wrote:
>>>
>>>A simple hack could be:
>>>1) Create a working table that has an identity field
>>>2) Insert the data into the working table using the order by clause of
>the
>>>select statement to force the correct ordering.
>>>
>>>Share & Enjoy
>>>PP
>>>
>>>
>>>"tag" <taglia@mediaone.net> wrote:
>>>>
>>>>Using SQLServer 7, In addition to returning a group of records from a
>pretty
>>>>basic select statement, I have a need to insert record numbers to each
>>row.
>>>> The numbers are to be calculated 1-X based on the results of the query
>>>(not
>>>>their position in the table). In other words, there are a total of 4
>fields
>>>>needed for a text output file (zip, lat, lon, and recnum). The zip,
lat,
>>>>and lon fields reside in one table, however the recnum field needs to
>be
>>>>added so that each row is numbered sequentially
>>>>
>>>>zip lat lon RECNUM
>>>>
>>>>01501, 084, 180,1
>>>>99925, 033, 223,2
>>>>etc..
>>>>
>>>>I tried declaring a cursor and looping through the records but I must
>be
>>>>be doing something wrong. I am hoping to:
>>>>
>>>>1) Use DTS to run the select statement to grab the records that I am
looking
>>>>for
>>>>2) Add a recnum to the record set
>>>>3) Output the results to a delimited text file.
>>>>
>>>>I have the whole thing working except for the record counting. I would
>>>appreciate
>>>>any help.
>>>>
>>>>Paul
>>>>
>>>
>>
>

[Mike VanB.]

Probably a better (less expensive than TRUNCATE) way to do this would be to
create a temporary table in the statemant. This could all be wrapped up in a
stored proc.

CREATE TABLE #Temp(
CountID int IDENTITY,
OtherColumns varchar(32)
)

INSERT INTO #Temp (OtherColumns)
SELECT OtherColumns FROM tTable

/*
Now do whatever you need with the data in the temp table
*/

DROP TABLE #Temp

****************
Mike VanBeneden


"paul" <taglia@medianone.net> wrote in message
news:39a43c6a$1@news.devx.com...
>
> THANK YOU MUCH! You were spot on with the TRUNCATE TABLE command. It is
> working perfectly now!!!!
>
> Best regards,
>
> Paul
>
>
> "PP" <PunkPuffin@Tesc.net> wrote:
> >
> >Try using TRUNCATE TABLE (if you are the owner) or select into a temp
table
> >defining one of the columns as IDENTITY(int,1,1) or the DBCC CHECKIDENT
> which
> >is for the sysadmin group. Or use an insert trigger instead of the auto
> incrementing
> >INDENTITY which might slow things down.
> >
> >Share & Enjoy
> >
> >
> >
> >"Paul" <taglia@mediaone.net> wrote:
> >>
> >>Thanks for your recommendation. I did create a new table, set an ID
field,
> >>set the seed value to 1 and increment to 1 also. This method comes very
> >>close to the perfect solution. The only issue is that the id field
continues
> >>to increment the number even though I delete all rows before writing to
> >the
> >>table.
> >>
> >>Without getting too detailed, the main table holds the GPS position of
> >a
> >>fleet of 20 tractor trailers. Since I pick the data up every 15
minutes,
> >>the main table has thousands of rows with timestamped position data.
The
> >>select statement that I run simply reports the most recent sighting of
> the
> >>20 trucks - i.e. the query returns one record for each truck. Since I
> am
> >>using this new table to send to a mapping application, I clear the new
> table,
> >>then update the table with the latest position data. Again, the only
issue
> >>is that the counter does not reset by emptying out the table.
> >>
> >>Is there any way to have a record counting field in the latest position
> >table
> >>so that it resets to 1 each time I write to it?
> >>
> >>Thanks for any ideas...
> >>
> >>"PP" <PunkPuffin@Tesco.Net> wrote:
> >>>
> >>>A simple hack could be:
> >>>1) Create a working table that has an identity field
> >>>2) Insert the data into the working table using the order by clause of
> >the
> >>>select statement to force the correct ordering.
> >>>
> >>>Share & Enjoy
> >>>PP
> >>>
> >>>
> >>>"tag" <taglia@mediaone.net> wrote:
> >>>>
> >>>>Using SQLServer 7, In addition to returning a group of records from a
> >pretty
> >>>>basic select statement, I have a need to insert record numbers to each
> >>row.
> >>>> The numbers are to be calculated 1-X based on the results of the
query
> >>>(not
> >>>>their position in the table). In other words, there are a total of 4
> >fields
> >>>>needed for a text output file (zip, lat, lon, and recnum). The zip,
> lat,
> >>>>and lon fields reside in one table, however the recnum field needs to
> >be
> >>>>added so that each row is numbered sequentially
> >>>>
> >>>>zip lat lon RECNUM
> >>>>
> >>>>01501, 084, 180,1
> >>>>99925, 033, 223,2
> >>>>etc..
> >>>>
> >>>>I tried declaring a cursor and looping through the records but I must
> >be
> >>>>be doing something wrong. I am hoping to:
> >>>>
> >>>>1) Use DTS to run the select statement to grab the records that I am
> looking
> >>>>for
> >>>>2) Add a recnum to the record set
> >>>>3) Output the results to a delimited text file.
> >>>>
> >>>>I have the whole thing working except for the record counting. I
would
> >>>appreciate
> >>>>any help.
> >>>>
> >>>>Paul
> >>>>
> >>>
> >>
> >
>