my solution using self join


  • 0
    P
    SELECT sum(distinct i1.TIV_2016) as TIV_2016 FROM insurance i1, insurance i2 where i1.PID != i2.PID AND i1.TIV_2015 = i2.TIV_2015 AND (i1.LAT,i1.LON) NOT IN (
        select LAT, LON FROM insurance GROUP BY LAT, LON HAVING count(*) > 1
    )
    

  • 0
    P

    @pxj5333 said in my solution using self join:

    SELECT sum(distinct i1.TIV_2016) as TIV_2016 FROM insurance i1, insurance i2 where i1.PID != i2.PID AND i1.TIV_2015 = i2.TIV_2015 AND (i1.LAT,i1.LON) NOT IN (
    select LAT, LON FROM insurance GROUP BY LAT, LON HAVING count(*) > 1
    )

    Gives wrong result for

    {"headers":{"insurance":["PID","TIV_2015","TIV_2016","LAT","LON"]},"rows":{"insurance":[[1,4,10,12,10],[2,4,10,11,10],[3,10,10,10,10],[4,10,10,10,10]]}}


  • 0
    P

    @pradeepkv09 Hmm..... it is weird. Are you sure you use exactly the same query as I posted? I tried couple times and this can be accepted. Also technically it should be correct.
    Maybe you can copy and paste and try again:

    SELECT sum(distinct i1.TIV_2016) as TIV_2016 FROM insurance i1, insurance i2 where i1.PID != i2.PID AND i1.TIV_2015 = i2.TIV_2015 AND (i1.LAT,i1.LON) NOT IN (
    select LAT, LON FROM insurance GROUP BY LAT, LON HAVING count(*) > 1
    )


  • 0
    Y

    use distinct might lead to wrong answer.

    have a try to this case:

    {
        "headers":{"insurance":["PID","TIV_2015","TIV_2016","LAT","LON"]},
        "rows":{"insurance":[[1,10,5,10,10],
                             [2,20,20,20,20],
                             [3,10,30,20,20],
                             [4,10,40,40,40],
                             [5,10,40,111,111]]}
    }
    

Log in to reply
 

Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.