numbering records returned in a query


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 7 of 7

Thread: numbering records returned in a query

  1. #1
    tag Guest

    numbering records returned in a query


    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


  2. #2
    PP Guest

    Re: numbering records returned in a query


    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
    >



  3. #3
    Mokgobi Guest

    Re: numbering records returned in a query


    "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.


  4. #4
    Paul Guest

    Re: numbering records returned in a query


    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
    >>

    >



  5. #5
    PP Guest

    Re: numbering records returned in a query


    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
    >>>

    >>

    >



  6. #6
    paul Guest

    Re: numbering records returned in a query


    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
    >>>>
    >>>

    >>

    >



  7. #7
    Mike VanB. Guest

    Re: numbering records returned in a query

    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
    > >>>>
    > >>>
    > >>

    > >

    >




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center