Partition a list of names into folders by initial letter

Question

Given a set of data like:

id    Name
   1  Aaa
   2  Aab
   3  AAc
…
 999  Zzz

, I'd like to create virtual folders that partition it by initial letter. For example, I'd like to pass 7 to a function and get 7 folders, e.g.:

A-C
D-F
G-H
I-M
N-Q
R-S
T-Z

…which each contain the respective value (e.g. T-Z will contain Zzz). I've figured out that I can use NTILE() to get fairly close to the desired result:

WITH Ntiles(Name, Ntile) AS (
    SELECT Name, NTILE(7) OVER(ORDER BY Name) FROM #Projects
)
SELECT MIN(LEFT(N.Name, 1)) + '-' + MAX(LEFT(N.Name, 1))
FROM Ntiles N 
GROUP BY Ntile

To add the desired values, I'm doing another two joins:

WITH Ntiles(Name, Ntile) AS (
    SELECT Name, NTILE(7) OVER(ORDER BY Name) FROM #Projects
) SELECT P.Name, (
    SELECT MIN(LEFT(N1.Name, 1)) + '-' + MAX(LEFT(N1.Name, 1)) FROM Ntiles N1 
    WHERE N1.Ntile = N2.Ntile
GROUP BY N1.Ntile
) FROM #Projects P INNER JOIN Ntiles N2 ON P.Name = N2.Name

…which seems a bit inefficient.

But it's also wrong: there's overlap (for instance, the letter P appears both in J-P and P-T).

Am I on the right track? Is there a more efficient way? How do I prevent overlap?

Andriy M · Accepted Answer · 2011-05-07T19:36:22.260

2

If you simply need to split the initial letter list into fairly equal groups, then you should probably do that roughly the way you've been doing it until now, but in a slightly different order:

Get all the initial letters that are in the table.
NTILE the resulting list.
Group the NTILEd result set.

WITH letters AS (
  SELECT
    Letter = LEFT(Name, 1)
  FROM #Projects
  GROUP BY LEFT(Name, 1)
),
ntiled AS (
  SELECT
    Letter,
    Folder = NTILE(7) OVER (ORDER BY Letter)
  FROM letters
)
SELECT
  Folder,
  FolderCaption = MIN(Letter) + '-' + MAX(Letter)
FROM ntiled
GROUP BY Folder

edited May 07 '11 at 19:36

answered May 07 '11 at 19:31

Andriy M

76,112
17
94
154

Well, the letter groups *should* be weighted based on the original data, not based on letters. – Sören Kuklau May 08 '11 at 07:12
In my opinion, it would be fair if you revoked the acceptance of my answer *and* added your last piece of specification to your question. I'd try to find the right solution for you, or maybe someone else would manage to do that sooner than me. As an alternative, you could ask a new question, of course, if that suits you. – Andriy M May 08 '11 at 10:03

Partition a list of names into folders by initial letter

1 Answers1