I have a table with two fields:

idnum (index)
full_name

I'm trying to design a query, or set of queries, that will let me identify the records with the "lowest" (i.e., first in an
alphabetical sort) full_name, by first letter of full_name. In other words, if the data looked like this:

1 ABaa
2 AAaa
3 BCcc
4 BAaa
5 BAaa

The result would be

A AAaa 2
B BAaa 4 (ties on full_name go to the lowest idnum)

I can do this using cursors, but I'm curious if there's another way to achieve it.

[quoted text, click to view]

Untested:

select substring(full_name,1,1) as first_letter,
full_name,
min(idnum) as idnum
from the_table
where full_name in (
select min(full_name)
from the_table
group by substring(full_name,1,1)
)
group by substring(full_name,1,1),