Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2010
    Posts
    2

    Question Unanswered: getting the most common value

    Hi,

    I am using this query:

    Select
    t1.col1 as A,
    t2.col1 as B,
    t3.col1 as C,
    t4.col1 as D
    FROM table_1 t1
    left outer join table_2 t2 on t1.col1=t2.col1
    left outer join table_3 t3 on t1.col1=t3.col1
    left outer join table_4 t4 on t1.col1=t4.col1
    where t1.col2='E';

    with this above query i am getting result like this:

    A B C D

    2 null 2 null
    6 6 null 6
    8 null null null

    However i need the result like this:

    A B C D

    6 6 null 6
    2 null 2 null
    8 null null null


    Note:
    I am looking for common value that presents in most of these 4 tables for different t1.col2 values like 'E','G','K',...etc.
    I cant use the NOT NULL condition in the where clause,since i need to check for different values in t1.col2,for each of this there may be null value in any of the 4 tables.

    Thanks for looking,any help is appreciated.
    Sekar

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    mydbforum, I am not at a place where I can test this but this might get you what (I think) you want:
    Code:
    SELECT T1.COL1 AS A
         , T2.COL1 AS B
         , T3.COL1 AS C
         , T4.COL1 AS D
         ,   CASE WHEN T1.COL1 IS NULL THEN 0 ELSE 1 END
           + CASE WHEN T2.COL1 IS NULL THEN 0 ELSE 1 END
           + CASE WHEN T3.COL1 IS NULL THEN 0 ELSE 1 END
           + CASE WHEN T4.COL1 IS NULL THEN 0 ELSE 1 END AS MATCHES
    FROM (SELECT COL1
          FROM TABLE_1
          WHERE COL2 = 'E'
         ) AS T1
         LEFT OUTER JOIN
           TABLE_2 T2
           ON T1.COL1 = T2.COL1
             LEFT OUTER JOIN
               TABLE_3 T3
               ON T1.COL1 = T3.COL1
                 LEFT OUTER JOIN
                   TABLE_4 T4
                   ON T1.COL1 = T4.COL1
    ORDER BY MATCHES DESC
    Since both of you results have the same values, I am assuming you want to sort them so the row with the most matches come first and the ones with the least matches comes last.

    To do this, I am using a CASE expression to check the columns for NULL. If the column is NULL, A 0 is returned. Otherwise a 1 is returned. These values are added together to the the number of Matches. Then you can sort by this column value.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    mydbforum, can you give us a bit more insight into the real world problem that you're trying to solve? Stealth_DBA gave a good answer based on the information that you've provided, but I suspect that you've left out a lot of important considerations that would let us craft a much better solution.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Mar 2010
    Posts
    2

    Thumbs up

    Thanks Stealth_DBA,now it's working fine.Thanks for your help.


    Quote Originally Posted by Stealth_DBA View Post
    mydbforum, I am not at a place where I can test this but this might get you what (I think) you want:
    Code:
    SELECT T1.COL1 AS A
         , T2.COL1 AS B
         , T3.COL1 AS C
         , T4.COL1 AS D
         ,   CASE WHEN T1.COL1 IS NULL THEN 0 ELSE 1 END
           + CASE WHEN T2.COL1 IS NULL THEN 0 ELSE 1 END
           + CASE WHEN T3.COL1 IS NULL THEN 0 ELSE 1 END
           + CASE WHEN T4.COL1 IS NULL THEN 0 ELSE 1 END AS MATCHES
    FROM (SELECT COL1
          FROM TABLE_1
          WHERE COL2 = 'E'
         ) AS T1
         LEFT OUTER JOIN
           TABLE_2 T2
           ON T1.COL1 = T2.COL1
             LEFT OUTER JOIN
               TABLE_3 T3
               ON T1.COL1 = T3.COL1
                 LEFT OUTER JOIN
                   TABLE_4 T4
                   ON T1.COL1 = T4.COL1
    ORDER BY MATCHES DESC
    Since both of you results have the same values, I am assuming you want to sort them so the row with the most matches come first and the ones with the least matches comes last.

    To do this, I am using a CASE expression to check the columns for NULL. If the column is NULL, A 0 is returned. Otherwise a 1 is returned. These values are added together to the the number of Matches. Then you can sort by this column value.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •