Numbering of Records

  • Dear All,

    How can I get my returned records numbered from a simple SQL statement?

    Table A:

    Field 1    Field 2    Field 3

    =====   =====    =====

    Val 1a    Val 2a    Val 3a

    Val 1b    Val 2b    Val 3b

    Val 1c    Val 2c    Val 3c

    What is the query that will give me the following result - without sending this data in a temp table?

    1.   Val 1a    Val 2a    Val 3a

    2.   Val 1b    Val 2b    Val 3b

    3.   Val 1c    Val 2c    Val 3c

    Thanks & Regards

    Andreas

  • Can you live with a table variable???

    DECLARE @tempTableVariable TABLE (IDENT int IDENTITY(1,1), col1 varchar(10), col2 varchar(10), col3 varchar(10))

    INSERT INTO @tempTableVariable

    SELECT * FROM SourceTable

    select * from @tempTableVariable

    Yields:

    1 Val 1a Val 2a Val 3a

    2 Val 1b Val 2b Val 3b

    3 Val 1c Val 2c Val 3c

    Ryan

  • Dear Ryan,

    Thanks for your reply but this does not really help me as I am trying to get the mentioned result from a single statement.

     

    Regards,

    Andreas

  • Try this...

    SELECT count(*) RecNum,

           a.LastName

         FROM Northwind.dbo.Employees a join

              Northwind.dbo.Employees b

              on a.LastName >= b.LastName

         group by a.LastName

         order by a.LastName

    Steve

  • Thanks Steve but unfortunately this does not work.  It returns "1" for every record.

    Regards,

    Andreas

  • Andreas, Steve's solution is correct (and clever):

    Based on your example:

    Create TABLE temptable (col1 varchar(10), col2 varchar(10), col3 varchar(10))

    GO

    INSERT INTO temptable values('Val 1a', 'Val 2a', 'Val 3a')

    INSERT INTO temptable values('Val 1b', 'Val 2b', 'Val 3b')

    INSERT INTO temptable values('Val 1c', 'Val 2c', 'Val 3c')

    GO

    ________________________________________________

    Your SQL would be:

    SELECT count(*) RecNum, a.*

    FROM tempTable a

    inner join tempTable b on a.col1 >= b.col1

    group by a.col1, a.col2, a.col3

    order by a.col1, a.col2, a.col3

     

    Good Job Steve.

    Ryan

  • It looks like I cannot avoid using a temp table.  Therefore, I will go ahead with the proposed solutions.  Thanks guys.

    Regards,

    Andreas

  • Andreas, you can avoid a temp table - that is what Steve's solution was for.  Post your table name and properties if need be and we can post the exact SQL.

    Ryan

  • If I understand your requirements right, Steve's solution surely works. However, I think on larger tables the use of a temp table might yield better performance.

    Actually in the end this is presentational stuff, which should be handled at the client.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • Just found another link:

    http://support.microsoft.com/support/kb/articles/q186/1/33.asp&NoWebContent=1

    Wrong link. Sorry:

    http://support.microsoft.com/default.aspx?scid=kb;en-us;186133

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • This may be moot because using a temp table for a large count set is desirable, but what if the record count exceeded 2,147,483,647 (the count function returns and integer), do you have any other option?

    Ryan

  • You can use the function COUNT_BIG() instead which returns a bigint.

    It uses the same parameters as the count() function.

  • If the row count of *any* query exceeds the range of an INT, you'll have other more serious problems than row numbering. I would start with adding a WHERE clause.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • Hehe... that too .

    One might also consider buying more ram and cpu power to handle such a query without a where clause to avoid those unspoken problems.

  • Hi,

     Use this

    select

    rank() over (order by empname) as Indexnumber,* from emp

    this works fine in YUKON

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply