Simple Select

• prsnlid

  SSC Enthusiast

  Points: 110

  More actions

  April 27, 2005 at 8:38 am

  #68980

  I have a table OrderDetails. I want to give a select query. In the result i want to generate an autoincrement field like serial number? how can i use with a single Select.

  EXAMPLE:

  My OrderDetails Table contains :

  itemid     - Description (Column names)

  121        - Pencil

  125       -  Pen

  By giving a SELECT statement i want to get the result as

  slno          -   itemid        -  Description

  1                 121                Pencil

  2                 125                Pen

  How can i give the slno increment provision in a single Select statement????

   

   

   

• Wayne Lawton

  Hall of Fame

  Points: 3532

  More actions

  April 27, 2005 at 8:44 am

  #555256

  G'day,

  Here is one way to do what you are asking.  This methd relies on a temp table, but may be sufficient for your needs.

  DROP TABLE TestTbl

  GO

  create table TestTbl (

          MyID   int,

          MyText varchar(25)

  )

  insert into TestTbl (MyID, MyText)

  SELECT 1, 'RED' UNION ALL

  SELECT 5, 'BLUE' UNION ALL

  SELECT 4, 'YELLOW' UNION ALL

  SELECT 12,'GREEN'

  GO

  SELECT * FROM TestTbl

     SELECT SLNO = IDENTITY(int, 1, 1),

            MyID,

            MyText

     INTO   TestTbl2

     FROM   TestTbl

  SELECT * FROM TestTbl2

  Hope this helps

  Wayne

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  April 27, 2005 at 8:46 am

  #555259

  The "best" answer here is, to do this at the client. For some ideas on how to do this on the server have a look here: 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]