Top function in sql for each alphabet

If i have a table with a column values alpha, azera, beta, brown, charlie, cling

How do i get the values like azera, brown and cling since "al" in alpha comes first "az" in azera.. I started using this in SQL 2005.

Select top 1 columnA from TableA order by ColumnA desc 

It gives me only 1 result but i need to get for each alphabet A B and C.

Thank you in advance

Answers

Your question is a little unclear but I'm assuming you want the largest (when alphabetically sorted) value for each starting letter. That is, if it were a dictionary you want the last entry for a, the last entry for b, and so on. If so, try:

SELECT MAX(myCol)
FROM myTable
GROUP BY LEFT(myCol,1)

SQL Fiddle

I am going to speculate that by "alphabet" you mean "first letter". You can solve this using row_number():

select columnA
from (select columnA,
             row_number() over (partition by left(columnA, 1) order by columnA desc) as seqnum
      from TableA
     ) t
where seqnum = 1
order by columnA

I assume you want to partition by the first letter and get one word for every letter.

You could use a CTE with Row_Number function:

WITH cte 
     AS (SELECT ColumnA, 
                rn=Row_number() 
                     OVER( 
                       partition BY Substring(ColumnA, 1, 1) 
                       ORDER BY ColumnA DESC) 
         FROM   TableA) 
SELECT * 
FROM   cte 
WHERE  rn = 1 

Demo

Result:

azera   
brown   
cling       

Need Your Help