order by different start letter every day

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

order by different start letter every day

Author

Topic

colgie
Starting Member

18 Posts

Posted - 2010-08-09 : 14:56:39

Is there a way to ORDER BY starting with a different letter of the alphabet, Rather than A-Z I want to get all the results starting with B say and continuing until A. The next time perhaps ordering by results starting with C and continuing until B etc etc

IE: BCDEFGHIJKLMNOPQRSTUVWXYZA
Then CDEFGHIJKLMNOPQRSTUVWXYZAB

Any Help Greatly Appreciated

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-08-09 : 15:18:44

Proof of concept, change column and table names to meet your needs:

create table #z (name varchar(20))
insert #z select 'ABC' union all
select 'BCD' union all
select 'CDE' union all
select 'EFG' union all
select 'FGH' union all
select 'GHI' union all
select 'HIJ'

;with n(n) as (select 1 union all select n+1 from n where n<26),  -- generate numbers for loop below
-- use numbers to extract each character from the string
b(b,c) as (select substring('CDEFGHIJKLMNOPQRSTUVWXYZABCDEFGH',n,1) b, n c from n)
	select z.name from #z z
	inner join b b on substring(z.name,1,1)=b.b  -- join the first character to its sort order
	order by b.c, z.name

edit: added comments

colgie
Starting Member

18 Posts

Posted - 2010-08-09 : 16:03:42

This looks like just what I need. I'm having a little difficulty following exactly how it is doing it. I'd love to know so is there any chance you could briefly describe what it is doing bit by bit. Sorry to be a pain but I'm fairly shaky in SQL.
Looks like an awesome solution however and thanks a million anyway whether you can be bothered to spoon feed me or not.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-08-09 : 16:15:34

I added comments in green. I'm using a common table expression (CTE) to generate a list of numbers. These numbers are used by SUBSTRING() to extract each character at that numeric position. This is then joined to the first character of the Name in the other table. This JOIN relates the numeric position in the sort string to the name and lets you order by it.

colgie
Starting Member

18 Posts

Posted - 2010-08-09 : 16:25:00

Thanks again - I'll go to school on your solution and comments for as long as it takes to clear the fog in my brain. Really amazing speed and clarity from you. I cannot thank you enough.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources