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

Can I get sql profiler for sql2008 for free?

sql-server-express profiler sql-server-2008-express

Is it possible to get sql profiler for sql2008 for free or you have to have a license to get it?

Error: allowDefinition='MachineToApplication' beyond application level

asp.net visual-studio-2012

I have downloaded the online project in ASP.Net. While running application I get an error

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.