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.
Leave a Reply