irfan patel irfan patel - 5 months ago 8
SQL Question

How can I select the longest string when using GROUP BY for multiple columns in Sql Server

I have below tables with duplicates values. i want to take only max length text from it.

id Text no
1 lorem ipsum ipsum 8955
1 ipsum 6879668
1 lorem ipsum ipsum 5464
2 lorem ipsum ipsum derome 63465465
2 lorem ipsum derome ipsum derome 555454


My Query:

select id,MAX(text),MAX(no) from table1 group by id


Result:

id text no
1 lorem ipsum ipsum 8955
2 lorem ipsum ipsum derome 63465465


Expected Output:

id Text no
1 lorem ipsum ipsum 6879668
2 lorem ipsum derome ipsum derome 63465465

Answer

You seem to want both the longest text and the highest number looking at your expected results.

Let's make some test data;

IF OBJECT_ID('tempdb..#TestData') IS NOT NULL DROP TABLE #TestData
GO
CREATE TABLE #TestData (ID int, Text varchar(50), no int)
INSERT INTO #TestData (ID, Text, no)
VALUES
(1,'lorem ipsum ipsum',8955)
,(1,'ipsum',6879668)
,(1,'lorem ipsum ipsum',5464)
,(2,'lorem ipsum ipsum derome',63465465)
,(2,'lorem ipsum derome ipsum derome',555454)

I've done this as subqueries to get both max values separately. The first inner join is just for filtering, the second one returns the highest value from the no column.

SELECT DISTINCT
t.id
,t.Text
,n.MaxNo
FROM #TestData t
JOIN  --Get the max data length
    (
        SELECT 
        ID
        ,MAX(len(Text)) MaxLen
        FROM #TestData 
        GROUP BY ID
    ) m 
ON t.ID = m.ID
AND LEN(t.Text) = m.MaxLen
JOIN --Get the max no
        (
        SELECT 
        ID
        ,MAX(no) MaxNo
        FROM #TestData 
        GROUP BY ID
    ) n
ON t.ID = n.ID

The results come out as;

id  Text                            MaxNo
1   lorem ipsum ipsum               6879668
2   lorem ipsum derome ipsum derome 63465465