Home SQL find multiple longest column results with its id and collect them in a single query result

Questions

SQL find multiple longest column results with its id and collect them in a single query result

February 9, 2024

I need to find the records with the longest field answer, for example: To get the longest First_Name, Family_Name and Company, it returns 3 different records, 1 for every longest answer and its record id.

SELECT TOP 1 Id, LEN(First_Name)  
FROM Persons 
Group BY Id, LEN(First_Name) 
ORDER BY 2 DESC

Something like this would bring me the longest first_name, but I need to apply this for multiple fields and get multiple records in a single query

>Solution :

WITH LongestFirstNames AS (
    SELECT TOP 1 WITH TIES Id, First_Name
    FROM Persons
    ORDER BY LEN(First_Name) DESC
),
LongestFamilyNames AS (
    SELECT TOP 1 WITH TIES Id, Family_Name
    FROM Persons
    ORDER BY LEN(Family_Name) DESC
),
LongestCompanies AS (
    SELECT TOP 1 WITH TIES Id, Company
    FROM Persons
    ORDER BY LEN(Company) DESC
)
SELECT 'Longest First Name' AS Field, Id, First_Name
FROM LongestFirstNames
UNION ALL
SELECT 'Longest Family Name' AS Field, Id, Family_Name
FROM LongestFamilyNames
UNION ALL
SELECT 'Longest Company' AS Field, Id, Company
FROM LongestCompanies;

Demo: https://dbfiddle.uk/k06YSAor