locked
Can anyone provide some help tips on SQL query select statement RRS feed

  • Question

  • Hi, everyone

    Can anyone provide some help tips on SQL query select statement.

    Sample :

    Table1 :

    fieldID     fieldvalue

    01           value1

    02           value2

    03           value3

    04          value4

     

    Store procedure expect parameter for partition

    output:

    fieldID    partitionID     fieldvalue

    01            1                value1

    02            1                value2

    03            2                value3

    04            2               value4

    thanks

     

     

    Monday, October 25, 2010 6:37 AM

Answers

  • try this one -

    declare @tab table

    (

    fieldID varchar(5),

    fieldvalue varchar(20)

    )

    insert into @tab(fieldID,fieldvalue)

    select '01','value1'

    union

    select '02','value2'

    union

    select '03','value3'

    union

    select '04','value4'

     

    select fieldID,

           fieldvalue,

           NTILE(2) OVER (ORDER BY fieldID) AS 'partition value'

    from @tab

    Output:

    fieldID fieldvalue           partition value

    ------- -------------------- --------------------

    01      value1               1

    02      value2               1

    03      value3               2

    04      value4               2

    go to here for ranking function.

    Thanks,


    Kapil Khalas - Database Developer
    • Marked as answer by Ai-hua Qiu Tuesday, November 2, 2010 8:44 AM
    Monday, October 25, 2010 6:48 AM

All replies

  • Canm you please explain what is the logic of partitionID?
    Please visit my Blog for some easy and often used t-sql scripts
    Monday, October 25, 2010 6:43 AM
  • try this one -

    declare @tab table

    (

    fieldID varchar(5),

    fieldvalue varchar(20)

    )

    insert into @tab(fieldID,fieldvalue)

    select '01','value1'

    union

    select '02','value2'

    union

    select '03','value3'

    union

    select '04','value4'

     

    select fieldID,

           fieldvalue,

           NTILE(2) OVER (ORDER BY fieldID) AS 'partition value'

    from @tab

    Output:

    fieldID fieldvalue           partition value

    ------- -------------------- --------------------

    01      value1               1

    02      value2               1

    03      value3               2

    04      value4               2

    go to here for ranking function.

    Thanks,


    Kapil Khalas - Database Developer
    • Marked as answer by Ai-hua Qiu Tuesday, November 2, 2010 8:44 AM
    Monday, October 25, 2010 6:48 AM
  • Hi

    Try this solution, It will retun result as you expected, In this solution Field id should be Numeric,

    select fieldID,ceiling(fieldID/2) as partitionID  , fieldvalue from Table1

     

    Hope this will help you,

    Regards

    AtishRG

     

     

     


    AtishRG
    Monday, October 25, 2010 12:06 PM