Results 1 to 7 of 7

Thread: SQL Row count

  1. #1
    Join Date
    Aug 2003
    Posts
    3

    Unanswered: SQL Row count

    I am looking for a solution what returns me the number of rows from a query. In Sybase there is a function called Number(*) what creates a serial number (e.g. 1, 2, 3, 4, ..... till the end).
    Is there a function in SQL Server giving the same results (ROWCOUNT() only returns the number of rows from an executed query, but can't do an increment from the beginning).

    I appreciate any help, thanks, martin

  2. #2
    Join Date
    Jun 2003
    Location
    New York
    Posts
    5
    I believe you are looking for Count(*)

  3. #3
    Join Date
    Aug 2003
    Posts
    3
    thanks for replying, i also thought of count(*), but it does not work for my needs. I need an output what looks like this (see attached file, from the sybase database).

    thanks, martin
    Attached Thumbnails Attached Thumbnails number.jpg  

  4. #4
    Join Date
    Jul 2003
    Location
    London
    Posts
    26
    Originally posted by mwiesauer
    thanks for replying, i also thought of count(*), but it does not work for my needs. I need an output what looks like this (see attached file, from the sybase database).

    thanks, martin
    Hi

    I think the best you can do is as follows:-

    SELECT IDENTITY(int, 1,1) AS id, *
    INTO #temp1
    FROM <tablename>

    SELECT * FROM #temp1

    IDENTITY function will only work with SELECT INTO.

    Regards

    Richard...

  5. #5
    Join Date
    Aug 2003
    Posts
    3
    hi, thanks for helping me, i really appreciate.
    when i run the code i get an error message:

    Cannot add identity column, using the SELECT INTO statement, to table '#temp', which already has column 'ID' that inherits the identity property.

    Do i firstly need to create a temp table, or do other stuff?!?

    thanks, martin

  6. #6
    Join Date
    Jul 2003
    Location
    London
    Posts
    26
    Hi

    If the table already has an identity column, which it sounds like it does, then you will have to create a temp table, or just use the fields you need in the select statement. Unless of course you require ID field.

    Regards

    Richard...

  7. #7
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    Originally posted by gbd77
    Hi

    If the table already has an identity column, which it sounds like it does, then you will have to create a temp table, or just use the fields you need in the select statement. Unless of course you require ID field.

    Regards

    Richard...
    Your statement should be like

    SELECT IDENTITY(int, 1,1) AS RowNumber, T.ID+0 AS TableID, <other columns>
    INTO #temp1
    FROM <TableName> T

    Hope, this helps;
    Hoffentlich funktioniert es so

    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

Posting Permissions

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