locked
Query> No same subject will be selected in nearby column RRS feed

  • Question

  • User1623409651 posted

    Hi all,

    I have below table data , Now   i Have to write a query to select the records with distinct in near by column ,  there should be no same subject student near by each other . mean no adjacent column have the same subject student.

    Subject A Subject B
    A I
    B J
    C K
    D L
    E M
    F N
    G O
    H P

    Required OUT PUT

    A I E M
    B J F N
    C K G O
    D L H P

    Please suggest the way.

    Thanks

    Saturday, February 24, 2018 6:07 AM

Answers

  • User-166373564 posted

    Hi, 

    DECLARE @Tmp TABLE ([Subject A] VARCHAR(20),[Subject B] VARCHAR(20))

    insert into @Tmp values

    ('A','I'),

    ('B','J'),

    ('C','K'),

    ('D','L'),

    ('E','M'),

    ('F','N'),

    ('G','O'),

    ('H','P');with a as

    (

     select *,ROW_NUMBER() over(order by [Subject A]) as rownum

     from @Tmp

    ),

    b as

    (

      select *,ROW_NUMBER() over(order by[Subject A]) as row1 from a where rownum<=4

    ),

    c as

    (

     select *,ROW_NUMBER() over(order by[Subject A]) as row2 from a where rownum>4

    )

    select

    b.[Subject A] as col1,

    b.[Subject B] as col2,

    c.[Subject A] as col3,

    c.[Subject B] as col4 from b join c on b.row1=c.row2

    Screenshot

    https://i.imgur.com/uA9oqRJ.png

    Regards,

    Angie 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, February 27, 2018 6:16 AM

All replies

  • User-707554951 posted

    Hi Rameezwaheed

    I did not find the logical relationship between the result you wanted and the datasheet you provided, so can you give a detailed description of this?
    For example, if you have more than one row or column, what will the result be?

    Best regards

    Cathy

    Tuesday, February 27, 2018 3:13 AM
  • User1623409651 posted

    Thank you Cathy Zou for your reply,

    Basically I have different Students Enrolled in different Subjects , now i need to design sitting arrangement for them  based on Room capacity. i have one table with students and their subjects enrolled. like

    Student1 SubjectA
    Student2 SubjectA
    Student3 SubjectA
    Student4 Subject A
    Student7 SubjectB
    Student8 SubjectB
    Student9 SubjectB
    Student10 SubjectB

    Thanks

    Tuesday, February 27, 2018 5:11 AM
  • User-166373564 posted

    Hi, 

    DECLARE @Tmp TABLE ([Subject A] VARCHAR(20),[Subject B] VARCHAR(20))

    insert into @Tmp values

    ('A','I'),

    ('B','J'),

    ('C','K'),

    ('D','L'),

    ('E','M'),

    ('F','N'),

    ('G','O'),

    ('H','P');with a as

    (

     select *,ROW_NUMBER() over(order by [Subject A]) as rownum

     from @Tmp

    ),

    b as

    (

      select *,ROW_NUMBER() over(order by[Subject A]) as row1 from a where rownum<=4

    ),

    c as

    (

     select *,ROW_NUMBER() over(order by[Subject A]) as row2 from a where rownum>4

    )

    select

    b.[Subject A] as col1,

    b.[Subject B] as col2,

    c.[Subject A] as col3,

    c.[Subject B] as col4 from b join c on b.row1=c.row2

    Screenshot

    https://i.imgur.com/uA9oqRJ.png

    Regards,

    Angie 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, February 27, 2018 6:16 AM