none
t-sql get a cumulation column RRS feed

  • Question

  • Hi Experts,

    I have a table t1 there is one column c1:

    select c1 from t1

    --------output

    c1

    b

    x

    d

    f

    a

    g

    c

    How to write a query to get

    c1     Sort

    b       1

    x        2

    d        3

    f         4

    a       5

    g      6

    c      7

    I cannot use Row_Number, it requires a [order by] columns.

     

     

    Monday, February 6, 2012 3:42 PM

Answers

  • try :

    select c1,ROW_NUMBER() over (order by (select 1)) as sort from t1
    

     


    Best regards
    • Marked as answer by SSAS_user Monday, February 6, 2012 4:00 PM
    Monday, February 6, 2012 3:58 PM
  • There is no other solution except for identity field or ROW_NUMBER() (but for ROW_NUMBER you need to have a field that controls the correct order).
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Monday, February 6, 2012 3:44 PM
    Moderator
  • It's quite possible  that  next time

    select c1 from t1

    will return

    c1

    d

    f

    a

    b

    g

    x

    c

     

    Without ORDER BY output order depends on MSSS internals which are out of our control and may change unpredictably.


    Serg
    Monday, February 6, 2012 3:54 PM

All replies

  • There is no other solution except for identity field or ROW_NUMBER() (but for ROW_NUMBER you need to have a field that controls the correct order).
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Monday, February 6, 2012 3:44 PM
    Moderator
  • sort

    1

    2

    3

     is a new column or any other thing you want

     

    Monday, February 6, 2012 3:45 PM
  • Is a column I want, the table T1 only has one column C1. Thank you!
    Monday, February 6, 2012 3:49 PM
  • It's quite possible  that  next time

    select c1 from t1

    will return

    c1

    d

    f

    a

    b

    g

    x

    c

     

    Without ORDER BY output order depends on MSSS internals which are out of our control and may change unpredictably.


    Serg
    Monday, February 6, 2012 3:54 PM
  • Thank you Serg. I understand you point. But actually it's not comming from a table, the values are came from XML query, so I can ensure the order is stable since the XML contains will not change. The reason I ask this is because I need to get the order of those values. And I can ensure the original order is what I want.

    Monday, February 6, 2012 3:57 PM
  • try :

    select c1,ROW_NUMBER() over (order by (select 1)) as sort from t1
    

     


    Best regards
    • Marked as answer by SSAS_user Monday, February 6, 2012 4:00 PM
    Monday, February 6, 2012 3:58 PM
  • Badii, How did you find that?

    BTW I like this forum.

    • Edited by SSAS_user Monday, February 6, 2012 4:02 PM
    Monday, February 6, 2012 4:00 PM
  •  

    @SSAS_user, order (select 1)  keeps the original order of the SourceTable

     


    Best regards
    Monday, February 6, 2012 4:12 PM
  • The table is not an ordered set. (SELECT 1) used in the ORDER BY clause with return data in some random order and there is no guarantee that the data will always be returned in that exact order.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Monday, February 6, 2012 4:16 PM
    Moderator
  •  

    @SSAS_user, order (select 1)  keeps the original order of the SourceTable

     


    Best regards

    Reading BOL or other docs I see no MS obligatons to that feature. The may change it at will. And more, even now there may exist cases when it will not hold.
    Serg
    • Edited by SergNL Monday, February 6, 2012 4:26 PM
    Monday, February 6, 2012 4:25 PM