locked
Select Query to show same value in all rows matching specific condition RRS feed

  • Question

  • User945338570 posted

    hi,

    I have a table with data in following structure:

        CallNumber  | Duration
        1112            30
        1112            null
        1112            null
        1119            10
        1175            null
        1132            59
        1175            190

    I want result of select query as:

        CallNumber  | Duration
        1112            30
        1112            30
        1112            30
        1119            10
        1175            190
        1132            59
        1175            190

    I do not know how to achieve the above result I am using MySQL database.

    Monday, December 4, 2017 11:47 AM

All replies

  • User2103319870 posted

    You can try with below query

    SELECT CallNumber, CASE 
        WHEN Duration IS NULL THEN b.DurationValue
        ELSE Duration END AS Duration 
    FROM SampleTable a
    JOIN (SELECT Duration as DurationValue, CallNumber AS CNo FROM SampleTable WHERE Duration IS NOT NULL) b
    ON b.CNo = a.CallNumber
    Monday, December 4, 2017 9:30 PM
  • User269602965 posted

    The first answer is essentially creating a dynamic lookup table against the data table, which is good, but I would use

    JOIN(SELECT DISTINCT Duration as DurationValue, CallNumber ... etc

    to have distinct duration and call number pairs in the dynamic lookup.

    Tuesday, December 5, 2017 2:16 PM