T-SQL Query For Finding The Longest And Shortest names In A Table

Introduction

In SQL Server, we can find the Longest String Value and Shortest String Value from the same table. To find the longest and shortest values, there are many ways, but in this article, we will discuss some simplest ways.

Disclaimer: From this article, I am not sharing these are the only efficient ways to query or best approach. I am sharing some simple options. If you know better ways to achieve this, please share that in our comment section.

Finding Longest & Shortest Names

Approach One

We can achieve this by writing it as two T-SQL Statement. As for this example, lets take [Details].[StudentDetails] and the column as FirstName.

SELECT [StudentId]
      ,[FirstName]
      ,[LastName]
      ,[RegistrationNumber]
      ,[Degree]
      ,[CreatedDate]
  FROM [PracticalWorks].[Details].[StudentDetails]

Now lets write T-SQL Statement to get the Longest and Shortest First Names with Length.

  SELECT 
	TOP (1) [FirstName], LEN(FirstName) AS [Length]
  FROM [PracticalWorks].[Details].[StudentDetails]
  ORDER BY [Length] DESC , FirstName


SELECT 
	TOP (1) [FirstName], LEN(FirstName) AS [Length]
  FROM [PracticalWorks].[Details].[StudentDetails]
  ORDER BY [Length] , FirstName

The above T-SQL query results as,

The first T-SQL statement, results the Longest Name and its length and the second T-SQL statement results the Shortest Name and its length.

Approach Two

From the Approach one, we got the result as two different result sets. But this can be returned in one single result set. By using a Sub-Query and UNION we can achieve it,

SELECT * FROM (
				SELECT 
					TOP (1) [FirstName], LEN(FirstName) AS [Length]
				  FROM [PracticalWorks].[Details].[StudentDetails]
				  ORDER BY [Length] DESC , FirstName
			 ) AS LongestName
UNION 
SELECT * FROM (
				SELECT 
					TOP (1) [FirstName], LEN(FirstName) AS [Length]
				  FROM [PracticalWorks].[Details].[StudentDetails]
				  ORDER BY [Length] , FirstName
			) AS ShortestName 

The above T-SQL statement results as,

Whenever we are using the ORDER BY, then we cannot directly apply the UNION Operator, so we are nesting select statements in a sub query

Conclusion

In this article, we have discussed the different approaches to finding the Largest and Shortest Names. I assume you all found this article useful. We will discuss more T-SQL concepts in upcoming articles. Please share your feedback in the comment section.