Some Interesting and Useful SQL Commands

 SQL or in other words Structured Query Language is an essential skill which every developer would like to have in their arsenal. Its a powerful language to interact with SQL Database. Despite its popularity lot of developers struggle to use some basic commands. Here we are going to see some interesting and Useful SQL commands and their use with the help of an example.

Let me state the problem statement first and then we can try to solve the problem using SQL Query.

Problem Statement

We are given a table which describes the Train Departure Timings from different stations as shown in the figure below:

We are asked to return the results of second most recently departed train from each station. So from the example table above the result would contain the following records:

Lets see how we can get the result using SQL.

Solution

First of all we need to group the records based on the station name. Along with that we need to sort each group based on the departure timings.

In this case we would need two additional columns 

1. Row Number corresponding to each group.
2. Count of Number of records belonging to each station

We need the row number corresponding to each station group as we know that the result would contain the second last record belonging to each group which can be extracted using the row number and the Count of records belonging to each group.

1 2 3 4 5 6 7 8

with new_table as (Select * ,row_number() over (partition by Station order by DepartureTime DESC) as rowNum ,count(*) over (partition by Station order by DepartureTime DESC range between unbounded preceding and unbounded following) as Cnt from TrainSchedule) select * from new_table where rowNum = case when Cnt = 1 then 1 else Cnt - 1 end;

As shown in the SQL code above we are adding two new columns whose values are computed over the partition of the original records based on the station name which again are ordered by the Departure time in descending order.

Adding row number is straight forward but getting the number of records belong to each station group needs additional conditions such as specifying the range within which the records can be counted. In this query I have specified the range between unbounded preceding and unbounded following which basically means that we would like to count all the records from the beginning of the group until the end of the group. by default the range is unbounded preceding and current row  which makes the count same as the row number as shown below:

With the unbounded preceding and unbounded following condition the results obtained are as follows:

The later select query gives us the final results where we are selecting the records where the row number = Count of records belonging to each group - 1 if the count >  1 else we are selecting the first record itself.

Result

The final results is shown below:

Here we have touched upon few Interesting and useful SQL commands which does the partitioning of the records based on certain column using the partition by construct then ordering each partition by descending order using the order by <Column Name> DESC/ASC. Specifying the range using the range between construct and finally using the conditional case statement in the Select query.