CODEWITHSUNDEEP
sql-serversql-server-2008-r2datalength
Metaphor
Metaphor

Reputation: 374

Find records with Minimum length data

I have a table #tempTest with data like this:

ID  Name
1   A
2   AB
3   ABC
4   ABCD
5   ABCDE
6   ABCDEF
7   X
8   QRWXYZ

Now I need the shortest name from the table.

I've tried this way: 

SELECT TOP 1(name) Smallest  FROM #tempTest
GROUP BY name
ORDER BY LEN(name)

And that represents:

Smallest
A

But what I need is:

ID  Name
1   A
7   X

Upvotes: 0

Views: 138

Answers (2)

Shariful_Islam
Shariful_Islam

Reputation: 361

SELECT TOP 1 WITH TIES (name) Smallest  FROM #tempTest
GROUP BY name
ORDER BY LEN(name)

Upvotes: 2

David M
David M

Reputation: 72890

SELECT id, name FROM #tempTest
WHERE LEN(name) = (SELECT MIN(LEN(name)) FROM #tempTest)

Upvotes: 1

Related Questions