EXISTS subquery with DATE_SUB() function


  • 0
    L

    Find a record R1 that exists another record R2 where R2 is recorded in exactly the previous day of R1 and R2 has a lower temperature.

    SELECT Id FROM Weather AS W1 WHERE 
    EXISTS 
    (SELECT * FROM Weather AS W2
     WHERE W2.Date = DATE_SUB(W1.Date, INTERVAL 1 DAY) 
       AND W2.Temperature < W1.Temperature);

  • 0
    C
    This post is deleted!

  • 0
    D

    My answer is almost the same but get "Internal Error":

    select t.Id
    from Weather t
    where exists
    (select null
    from Weather w
    where w.Date = date_sub(t.Date, interval 1 day)
    and w.Temperature < t.Temperature);

    I tried on my computer (5.6.16 MySQL Community Server with Workbench 6.2), it worked well (4 records in the table just like the question showed).

    And I copied your answer to commit, got "Internal Error" too.

    I don't know what's wrong.


  • 0
    S

    How about select * in the exists statement? I tried exists(select 1....)but it gave me internal error while select * works well


  • 0
    D

    Thank you for your comment.
    I just submit again, this time it was accepted.
    And I don't know why the same sql failed a month ago.
    "select * ", "select null ", "select 1" are all work well this time.


Log in to reply
 

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