Partition By and Count

• ohio_bear_1234

  SSC Enthusiast

  Points: 127

  More actions

  August 24, 2009 at 11:23 pm

  #134397

  Hi, it is very difficult to make sql scripts to count Flag with making sequences.

  Oracle has its lag function to make sequence. I would like to know how to make same function or scripts. I look into Partition by but do not understand much.

  Example,

  id flag

  1 T

  1 F

  1 T

  2 F

  2 T

  2 F

  2 T

  2 F

  3 T

  3 F

  3 T

  I want to transform data the above into:

  id flag seq

  1 T 1

  1 F 1

  1 T 2

  2 F 0

  2 T 1

  2 F 1

  2 T 2

  2 F 2

  3 T 1

  3 F 1

  3 T 2

  Thanks in advance

• Dave Ballantyne

  SSC-Dedicated

  Points: 33667

  More actions

  August 25, 2009 at 1:35 am

  #1044014

  create table yourtab

  (

  id integer,

  Flag char(1)

  )

  go

  insert into yourtab values(1,'T')

  insert into yourtab values(1,'F')

  insert into yourtab values(1,'T')

  insert into yourtab values(2,'T')

  insert into yourtab values(2,'T')

  insert into yourtab values(2,'T')

  insert into yourtab values(3,'F')

  insert into yourtab values(3,'F')

  insert into yourtab values(3,'T')

  go

  Select Id,Flag,Row_number() over (partition by Id,Flag Order by id,flag)

  from yourtab

  ---

  
  Clear Sky SQL
  My Blog[/url]

• ohio_bear_1234

  SSC Enthusiast

  Points: 127

  More actions

  August 25, 2009 at 2:17 am

  #1044021

  Thanks!!

  Great!