Query help

• khushbu

  SSCarpal Tunnel

  Points: 4591

  More actions

  November 15, 2009 at 11:15 pm

  #216715

  Hi

  I have a requirement where col 3 should contain values as in sample below (col1 and col2 are already populated)

  Col1 col2 col3

  abc 1 1

  abc 2 1

  abc 3 1

  fgh 1 2

  dft 1 3

  fgh 2 2

  abc 4 1

  Can someone pls help

  I know I can use update statement and group by clause but it will take time for millions of records .. hence i'm looking for some function like rank(), dense rank() but somehow not able to do it

• LutzM

  SSC Guru

  Points: 107049

  More actions

  November 16, 2009 at 12:19 am

  #1079514

  Would you mind showing us what you've done so far and where you got stuck?

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• vihi.koyott

  SSC-Addicted

  Points: 483

  More actions

  November 17, 2009 at 12:20 am

  #1080062

  Hi,

  Do you want a query output in the above format or you want to populate your table(3rd column) ?

  I created a table ABC with 2 columns .

  col1 col2

  abc1

  abc2

  abc3

  fgh1

  dft1

  fgh2

  abc4

  Executed the following query:

  select col1,col2,dense_rank() over (order by col1) as 'col3' from dbo.ABC

  it gave me :

  col1 col2 col3

  abc11

  abc21

  abc31

  abc41

  dft12

  fgh23

  fgh13

  is this what u want ?