Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    15

    Unanswered: Simple and Silly SQL Puzzle

    Hello SQL Gurus,

    I want to dynamically add a column to a select statement who must contain the line number.

    Example :

    Some table has 3 columns (id,field1,field2)


    SELECT top 3 *,[lineNumber] as lineOrder FROM someTable

    must produce

    id | field1 | field 2 | lineOrder
    -----------------------------------
    1 abc def 1
    23 def ghi 2
    7 ghi jkl 3

    and so on... Possible ?

  2. #2
    Join Date
    Feb 2004
    Location
    Poland
    Posts
    32

    Lightbulb Re: Simple and Silly SQL Puzzle

    Code:
    create table #tmp ([id] int, field1 varchar(3), field2 varchar(3), lineOrder int identity(1,1))
    insert into #tmp ([id], field1, field2) select id, field1, field2 from SomeTable
    select * from #tmp order by lineOrder
    drop table #tmp

  3. #3
    Join Date
    Mar 2004
    Posts
    15
    Great!!!

    But is there a way to do the same thing w/o having to resort to a temp table... like :

    select *, identity(1,1) as lineNum from someTable...

  4. #4
    Join Date
    Feb 2004
    Location
    Poland
    Posts
    32
    There is a function:
    IDENTITY ( data_type [ , seed , increment ] ) AS column_name
    but can be used only in a SELECT statement with an INTO table clause.

    Don't know any one-line solution.

  5. #5
    Join Date
    Jan 2003
    Location
    India
    Posts
    523

    Re: Simple and Silly SQL Puzzle

    how about:


    SELECT
    (SELECT COUNT(*) FROM customers b WHERE b.custid < a.custid) + 1 AS Number,
    *
    FROM customers a



    replace the table name and the column names in the above query with appropriate names.

    regards,
    Harshal.

  6. #6
    Join Date
    Feb 2004
    Location
    Poland
    Posts
    32

    Re: Simple and Silly SQL Puzzle

    Originally posted by harshal_in
    SELECT
    (SELECT COUNT(*) FROM customers b WHERE b.custid < a.custid) + 1 AS Number,
    *
    FROM customers a
    Looks good. I wonder which works faster.

    Rollmops - if you make any tests, any backinfo will be welcome.

Posting Permissions

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