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:

SQL Table listing the data

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:

Result of the SQL command execution

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:

Specifying the range in SQL

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

Correct range conditions results

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:

Final result of the SQL Query

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.
 

Comments

Post a Comment

Popular posts from this blog

Creating RESTful Minimal WebAPI in .Net 6 in an Easy Manner! | FastEndpoints

-
Image
  We might have developed Web APIs using MVC Controllers where we used to give Route Path Attributes to the Controller and  to identify individual methods we used to provide Actions in the Route. With the Controller approach we need to write boiler plate code in separate files which deals with the Configuration of the Web Service. Those who have worked in Node js would be aware of how easy it is to create a Web API and expose different Endpoints without the need of so much boiler plate code. Here we are going to discuss of similar approach for creating a Web API in .Net6 using FastEndpoints. Under the hood it uses Minimal API concept which tries to reduce the dependencies required to achieve a particular task. This approach is more suitable in the case of Microservice Architecture where we want to keep the dependencies to minimum. Must Read : Fluent API and Data Annotations What are FastEndpoints? FastEndpoints is a Framework compatible with .Net 6 which allows to build a...
Read more

Step By Step Guide to Detect Heap Corruption in Windows Easily

-
Image
Introduction Heap memory corruption is a critical issue that can lead to application crashes, data loss, and even security vulnerabilities. Detecting and addressing heap memory corruption early is crucial for maintaining the stability and security of Windows applications. In this blog post, we will explore how to detect heap memory corruption using two powerful tools: DebugDiag and AppVerifier. Must Read : synchronization in C++20 . Understanding Heap Memory Corruption Heap memory is a dynamically allocated memory space used by applications to store data at runtime. Heap memory corruption occurs when an application writes data beyond the allocated memory boundaries , leading to memory leaks, crashes, and unpredictable behavior. Detecting such corruption manually can be time-consuming and complex, making automated tools invaluable for identifying and diagnosing these issues. What is PageHeap? PageHeap specifically focuses on detecting heap-related problems, like heap overflows, heap und...
Read more

How to dynamically add Properties in C# .NET?

-
Image
There are certain situations where we have to create object dynamically with properties added to the object dynamically. Data related to these properties may be coming in from external sources like a file or from any other serialized format. We can achieve this with the help of reflection, but it is bit complex and should have some prior knowledge of Reflection. What is meant by Dynamic Creation of Properties in c#? Dynamic creation of properties means at the time of writing the code, programmer is not aware of the properties which can be part of the object and its value. For Example there may be some data which needs to be read from say xml file, In this case there should be some organized way by which we can represent the data, This can be in the form of a object. Content of this object and the properties cannot be deduced while writing the application, it needs to be constructed from the xml file which is read when the application runs. Expando Object Expando Object comes to the res...
Read more