Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Posts
    2

    Unanswered: row count column

    I am doing an sql query against multiple linked tables.

    Output is:

    Field1 Field2 Field3
    aaa bbb ccc
    bbb ddd sss
    ccc rrrr 555

    want it to look like:

    Counter Field1 Field2 Field3
    1 aaa bbb ccc
    2 bbb ddd sss
    3 ccc rrrr 555

    Basicly looking for column one to have which row number is in the results, however many rows there are.

    Kind of like an autonumber in Access.

    Sounds simple, but having trouble figuring out this one!

    Any help would be appreciated.

    Thanks,

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This is easy to do on the client. It is hard (and mostly meaningless) to do using SQL.

    The simplest way to do on the server side it is to create a temporary table with a count column. After you've built all of the rows into the temp table, create a cursor and number the rows. Keep in mind that you are going to a lot of extra work that is meaningless in a relational context... This is purely a crutch to make identifying rows even easier that is already very easy on the client, but it means nothing to the server.

    I'd very strongly suggest doing the numbering on the client side.

    -PatP

  3. #3
    Join Date
    Apr 2004
    Posts
    2

    Server side

    Really need it as part of the select statement, if possible.

    Anybody??

    Thanks,

  4. #4
    Join Date
    Feb 2004
    Posts
    108
    Include ROWNUM in the select statement.

    BTW, any drawbacks ?
    Order by clause will disturb your rownums.

  5. #5
    Join Date
    Feb 2004
    Posts
    108
    Originally posted by pinakdb
    Include ROWNUM in the select statement.

    BTW, any drawbacks ?
    Order by clause will disturb your rownums.
    Oops .... it's access ! That was only for oracle.
    Sorry !

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54

    Re: Server side

    Originally posted by PDK
    Really need it as part of the select statement, if possible.
    We all have to deal with ILCs sometimes. What database engine are you using?

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •