Return Serial Number As Per change Records

• Gaurav Srivastava

  SSChasing Mays

  Points: 621

  More actions

  April 15, 2015 at 8:46 pm

  #316648

  Sir

  I have a five rows such AS

  pd_id pd_name

  11 Prod-1

  22 Prod-2

  33 Prod-2

  44 Prod-3

  55 Prod-4

  When I Use Query

  Select sr_no=ROW_NUMBER() OVER(ORDER BY pd_name),pd_id,pd_name

  From Product

  it display

  sr_no pd_id pd_name

  1 11 Prod-1

  2. 22 Prod-2

  3. 33 Prod-2

  4. 44 Prod-3

  5. 55 Prod-4

  is it possible to display when change of product????

  sr_no pd_id pd_name

  1 11 Prod-1

  2 22 Prod-2

  2 33 Prod-2

  3 44 Prod-3

  4 55 Prod-4

  means on prod-2 should be same serial number.

  kindly suggest??????

• Jeff Moden

  SSC Guru

  Points: 1004600

  More actions

  April 15, 2015 at 9:43 pm

  #1791234

  itsgaurav (4/15/2015)

  ---

  Sir

  I have a five rows such AS

  pd_id pd_name

  11 Prod-1

  22 Prod-2

  33 Prod-2

  44 Prod-3

  55 Prod-4

  When I Use Query

  Select sr_no=ROW_NUMBER() OVER(ORDER BY pd_name),pd_id,pd_name

  From Product

  it display

  sr_no pd_id pd_name

  1 11 Prod-1

  2. 22 Prod-2

  3. 33 Prod-2

  4. 44 Prod-3

  5. 55 Prod-4

  is it possible to display when change of product????

  sr_no pd_id pd_name

  1 11 Prod-1

  2 22 Prod-2

  2 33 Prod-2

  3 44 Prod-3

  4 55 Prod-4

  means on prod-2 should be same serial number.

  kindly suggest??????

  SELECT sr_no = DENSE_RANK()OVER(ORDER BY pd_name)

  ,pd_id

  ,pd_name

  FROM dbo.Product

  ;

  ... and please see the article at the first link under "Helpful Links" in my signature line below for future posts, please.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)