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
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)
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
azera brown cling