Order By with Break - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Order By with Break

Author

Topic

nathans
Aged Yak Warrior

938 Posts

Posted - 2006-01-26 : 14:38:40

I came up with this quick solution today, but wanted to throw it out there to see if you guys had a better suggestion.

Basically, I want to take in a first letter param and order a name starting with that letter. In my example, I take in a parameter (A-Z) and order the letter column by the letter, but starting with the param input. So, if I had A-Z letter and inputted 'F', I want to order F-Z followed by A-E.

declare @letter table (letter char(1))
declare @i int,
	@break int

set @break = ascii('F')

-- populate @letter
set @i = 65
while @i <= 90
begin
	insert into @letter
	select char(@i)
		
	set @i = @i + 1
end


select letter
from (	select 	letter, 
		case when ascii(letter) between @break and max(ascii(letter)) then 1 else 2 end ordercol
	from @letter
	group by letter) d
order by d.ordercol

Nathan Skerl

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-26 : 20:04:44

Try the following:
Declare @s varchar(200), @s1 varchar(1)
Set @s = 'F' -- or received as parameter
Set @s = 'Select * from urtbl where ordercol like ''[' + @s1 + '-Z]%'' order by ordercol '
Set @s = @s + ' Union ' + 'Select * from urtbl where ordercol like ''[A-' + @s1 + ']%'' order by ordercol '
Exec (@s)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-26 : 22:20:33

[code]declare
@letter char(1)
select @letter = 'F'

select char(ascii('A') + n) as letter
from
(
select 0 as n union all select 1 union all select 2 union all select 3 union all
select 4 union all select 5 union all select 6 union all select 7 union all
select 8 union all select 9 union all select 10 union all select 11 union all
select 12 union all select 13 union all select 14 union all select 15 union all
select 16 union all select 17 union all select 18 union all select 19 union all
select 20 union all select 21 union all select 22 union all select 23 union all
select 24 union all select 25
) as num
order by case when char(ascii('A') + n) < @letter then 1 else 0 end, char(ascii('A') + n)[/code]

----------------------------------
'KH'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-27 : 05:41:01

Good logic Tan

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-27 : 08:04:11

quote:
Originally posted by madhivanan

Good logic Tan

Madhivanan

Failing to plan is Planning to fail

Thanks

----------------------------------
'KH'

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-01-27 : 10:00:13

I like using the MOD operator. Avoids temp tables:

declare	@Breakpoint char(1)
set	@Breakpoint = 'F'

select	*
from	[YourTable]
order by (Char(Upper(left(ordercolumn,1))) - char(upper(@Breakpoint))-130) % 25,
	ordercolumn

nathans
Aged Yak Warrior

938 Posts

Posted - 2006-01-27 : 13:54:33

Yea, nice solutions. Both of you!

Thanks

Nathan Skerl

nathans
Aged Yak Warrior

938 Posts

Posted - 2006-01-27 : 14:47:15

Though Blindman, I had to modify your example... is this what you had in mind?

declare @person table (first_name varchar(25))
insert into @person
	select 'adam' union select 'bob' union select 'chuck' union select 'dave' union select 'edward' union
	select 'frank' union select 'greg' union select 'henry' union select 'ira' 

declare	@Breakpoint char(1)
set	@Breakpoint = 'c'

select	first_name
from	@person
order by (ascii(Upper(left(first_name,1))) - ascii(upper(@breakpoint))-129)%26, first_name

Nathan Skerl

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-01-27 : 16:28:24

Something as simple as this should work:


order by 
  case when FirstName < @Breakpoint then 1 else 0 end ASC, 
  FirstName ASC

EDIT: I just noticed this solution was already posted.. NEVER MIND! :)

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-01-27 : 16:52:25

quote:

Originally posted by nathans

Though Blindman, I had to modify your example... is this what you had in mind?

declare @person table (first_name varchar(25))
insert into @person
	select 'adam' union select 'bob' union select 'chuck' union select 'dave' union select 'edward' union
	select 'frank' union select 'greg' union select 'henry' union select 'ira' 

declare	@Breakpoint char(1)
set	@Breakpoint = 'c'

select	first_name
from	@person
order by (ascii(Upper(left(first_name,1))) - ascii(upper(@breakpoint))-129)%26, first_name

Nathan Skerl

Looks OK. Sorry, I drafted my code blind on notedpad, since I was not at a server at the time.
Now, the question is whether my MOD formula is more efficient than jsmith8858's CASE solution, which is definitely simpler.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources