Database and SQL – 2

The post focuses on providing a thought to make comparison for values on the same column in SQL query. The idea came up with the Leetcode exercise “Rising Temperature” and “Consecutive Numbers”.

As a long time Excel user, I usually make comparison for values in one column by use formula. The below is an example using “IF” function to make comparison between values within the same column. Then we can pick up values based on our criteria by filtering the new column (with TRUE or FALSE).

However, there is not a such way to do comparison in SQL. Let’s take the “Rising Temperature” to demonstrate. The following is table “Weather” in an SQL Schema. The data type for the three columns are integer, date and integer respectively.

So, if we want to get all dates which a temperature higher than the previous day, we can’t really follow the same way as what we do in Excel. And there is what I did in SQL to achieve a such goal.

SELECT a.id
FROM Weather as a, Weather as b
WHERE DATEDIFF(a.recordDate, b.recordDate) = 1 AND a.Temperature > b.Temperature

The code effectively did this: assume it copied the table twice and the two copies are exactly same table as the original at the beginning. Then we shift the second table one day early by using the Datediff function in the WHERE clause. Assume we make the two tables align each other, the row 1 (header skipped) of table 1 is day 1 and is day 2 in table 2. So, we can compare the temperature between the two. After the comparison, we filter the results for days having a higher temperature than previous days by adding another condition using “AND” in the WHERE clause. It acts like the filter function in Excel. Here is a brief graph to show what it does.

Please visit my github SQL repository for more codes: https://github.com/eddyzhang2018/SQL