Find records with Minimum length data

Question

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

score 2 · Accepted Answer · answered Sep 10 '15 at 10:44

2

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

answered Sep 10 '15 at 10:44

Shariful_Islam

1

might be worth adding some information about the `WITH TIES` syntax as it's not that common. MSDN: https://msdn.microsoft.com/en-us/library/ms189463.aspx. *WITH TIES Used when you want to return two or more rows that tie for last place in the limited results set. Must be used with the ORDER BY clause.* – Tanner Sep 10 '15 at 10:55

score 1 · Answer 2 · answered Sep 10 '15 at 10:38

1

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

answered Sep 10 '15 at 10:38

David M

2 Answers2