Post reply

Have most occurrence column in result set

  • February 4, 2018 at 7:09 pm

    #403086

    I have result set with 4 columns. for example my column names Routername,a,b,c in result set.  for a columns contains multiple b and c. at first i need to take most occurrence value in b based on routername. and then i need to most occurrence value in c based on b.

    Routername abc
    R1CGM1switch1ip1
    R1CGM2switch1ip1
    R1CGM3switch2ip2
    R1CGM3switch1ip1

    I the above result i need get Routername = R1, a= cgm1, b=switch1, c= ip1
    first check most occurrence column in b is switch1 for r1 routername and then i need to check what is the most occurrence "c" value for this switch 1 is ip1.

    Please help me fix this issue. i need it immediately

    Thanks

  • February 4, 2018 at 7:37 pm

    #1978489

    So what's the answer supposed to be? What should the query return?

    Here's how to post sample data...
    CREATE TABLE RouterData (
        RouterName char(2),
        A char(4),
        B char(7),
        C char(3)
    );    
    GO
    INSERT INTO RouterData (RouterName, A, B, C)
    VALUES
    ('R1','CGM1','switch1','ip1'),
    ('R1','CGM2','switch1','ip1'),
    ('R1','CGM3','switch2','ip2'),
    ('R1','CGM3','switch1','ip1');

  • February 4, 2018 at 9:19 pm

    #1978495

    Thanks for the reply. But I need to get data from the resultant set(which contain multiple joins)

  • February 4, 2018 at 10:19 pm

    #1978499

    The script wasn't for you, it was for the people trying to help you. Since you're new, I think you should read Jeff Moden's article on how to post. It's just one of those things you should know how to do so that you don't spend a lot of time posting and not helping people help you.

    Instead of your explanation, please post what the answer should be.

    Does this work for you?
    SELECT TOP 1 x.RouterName
      , x.A
      , x.B
      , x.C
        , x.F
    FROM
      (SELECT RouterName
       , A
       , B
       , C
       , COUNT(*) OVER (PARTITION BY RouterName, B) AS F
      FROM RouterData) x
    ORDER BY x.F DESC;

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply