how to result query with repeat sequence number

• gentong.bocor

  Default port

  Points: 1480

  More actions

  March 5, 2015 at 7:42 pm

  #295023

  hi.. i want to make a query with the result like this, where Seqno is the result query which has repeating sequence number for same Field1

  Field1Field2SeqNo

  BVFUSBVFUS011

  BVFUSBVFUS021

  BVFUSBVFUS031

  BVFRPBVFRP012

  BVFRPBVFRP022

  BVFRPBVFRP032

  PCUSPCUS013

  PCUSPCUS023

  PCUSPCUS033

  PCRPPCRP014

  PCRPPCRP024

  PCRPPCRP034

  AdvUSAdvUS015

  AdvUSAdvUS025

  AdvUSAdvUS035

  GLUSGLUS016

  GLUSGLUS026

  GLUSGLUS036

  i want the result also no ordered by Field1. it just as the natural order of the table..

  Thanks

• happycat59

  One Orange Chip

  Points: 29389

  More actions

  March 5, 2015 at 8:01 pm

  #1782074

  Try something like

  select

  Field1

  , Field2

  , DENSE_RANK () OVER (ORDER BY Field1) AS SeqNo

  FROM YourTable

• gentong.bocor

  Default port

  Points: 1480

  More actions

  March 5, 2015 at 9:56 pm

  #1782083

  happycat59 (3/5/2015)

  ---

  Try something like

  select

  Field1

  , Field2

  , DENSE_RANK () OVER (ORDER BY Field1) AS SeqNo

  FROM YourTable

  yes.. it's get repeated seqno now based on field1, but it's sorted by field1.

  it's not the normal order from source table

• Eirikur Eiriksson

  SSC Guru

  Points: 182911

  More actions

  March 5, 2015 at 11:37 pm

  #1782086

  gentong.bocor (3/5/2015)

  ---

  happycat59 (3/5/2015)

  ---

  Try something like

  select

  Field1

  , Field2

  , DENSE_RANK () OVER (ORDER BY Field1) AS SeqNo

  FROM YourTable

  yes.. it's get repeated seqno now based on field1, but it's sorted by field1.

  it's not the normal order from source table

  Quick note, there is no such thing as a default sort order, use the order by clause to set the order on retrieval.

  😎