Sql Query

• shubham.saxena67

  Default port

  Points: 1490

  More actions

  January 28, 2011 at 6:36 am

  #231167

  Hello Friends,

  I am having a table Item_Master that are having more than 2 crore rows and the structure of table is

  as

  code item_code

  1 RS344

  1 RS344

  1 RS344

  2 RS355

  2 RS355

  3 RS356

  3 RS356

  3 RS356

  3 RS356

  i need append a column with value in that column like

  code item_code Item_value

  1 RS344 1

  1 RS344 2

  1 RS344 3

  2 RS355 1

  2 RS355 2

  3 RS356 1

  3 RS356 2

  3 RS356 3

  3 RS356 4

  the result contain only a index column inwhich indexing start from one up to....n and is depend on grouping 'code' field

  what is the shortest way to get this result.

  is there any way by which we can use 'partition by' clause in sql statement?

  or any other way?

  send whole sql select statement or function or cursor which make it fast?

  Thanks!!

• Mike01

  SSChampion

  Points: 11381

  More actions

  January 28, 2011 at 7:08 am

  #1279687

  what have you tried? I won't provide the code right away, since it doesn't look like you've given it a try. Depending on the size of the table, the approach may differ. I would probably create a temp table with the indexing that you require, then use a cte to insert into it. The cte would generate the new code on the fly. Then you can rename the tables and drop the original table.

  For better, quicker answers, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• tertiusdp

  SSCommitted

  Points: 1569

  More actions

  January 28, 2011 at 10:05 am

  #1279805

  Please add the ddl and row insert statements next time.

  This is just a quick and dirty to do the numbering...

  declare @test-2 table (code int , item_code nvarchar(10) )

  INSERT INTO @test-2 (code , item_code)

  SELECT 1 ,'RS344'

  UNION ALL SELECT 1, 'RS344'

  UNION ALL SELECT 1, 'RS344'

  UNION ALL SELECT 2, 'RS355'

  UNION ALL SELECT 2, 'RS355'

  UNION ALL SELECT 3, 'RS356'

  UNION ALL SELECT 3, 'RS356'

  UNION ALL SELECT 3, 'RS356'

  UNION ALL SELECT 3, 'RS356'

  SELECT

  ROW_NUMBER() OVER (PARTITION BY code,item_code ORDER BY code,item_code)

  ,*

  FROM @test-2

• shubham.saxena67

  Default port

  Points: 1490

  More actions

  January 28, 2011 at 10:08 pm

  #1280029

  Thanks Friend!!

  You have solved my problem i am vary thankful to u.

  Thank u again!!