Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2010
    Posts
    51

    Unanswered: Auto increment column in select query

    Hello Experts,

    This question has forever eluded me. So posting here.

    How can we generate a unique number for every row returned.

    for example:

    Code:
    Select id = identity(10), Name, Address
    from Table1
    Above query gives you syntax error saying you can only generate id if you are actually inserting into a table and not in the select statement.

    I know this is possible in Oracle but is this even possible in Sybase ASE 12.5?

    Hope the question is clear.

    Thanks,
    Meet
    Please always reply to the post if it was helpful. Others may find it helpful.

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    No, you'll have to insert into a temp table then select from the temp table
    Code:
    Select id = identity(10), Name, Address
    into #t1
    from Table1
    select * from #t1
    drop table #t1

  3. #3
    Join Date
    Jun 2010
    Posts
    51
    That's exactly my question. This means that this is not possible in sybase ASE? You have to insert into a temp table first?
    Please always reply to the post if it was helpful. Others may find it helpful.

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Yes, you have to insert into a temp table first.
    If your table isn't large and you have a unique key then you can generate it with a self join.
    Provide some sample data if you want an example.

Posting Permissions

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