Re: Listing of alternate rows using ORDER BY

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Steve Kass (skass_at_drew.edu)
Date: 11/07/04 

Date: Sat, 06 Nov 2004 19:03:22 -0500

And a bit better yet. There are many possibilities here...

create table #malefemale(name char(10), gender char(1))

insert #malefemale values ('bob', 'm')
insert #malefemale values ('ed', 'm')
insert #malefemale values ('joe', 'm')
insert #malefemale values ('dave', 'm')
insert #malefemale values ('sue', 'f')
insert #malefemale values ('joan', 'f')
insert #malefemale values ('kate', 'f')
insert #malefemale values ('mary', 'f')
insert #malefemale values ('mary', 'f')
insert #malefemale values ('mary', 'f')
go

create table GenderOut(
  N int not null,
  g char not null,
  p as isnull(10*N-9,0),
  pf as case when g = 'f' then 10*N-9 else 0 end,
  pm as case when g = 'm' then 10*N-9 else 0 end,
  primary key(p,g)
)
insert into GenderOut
select ProductID, g
from Northwind..Products
cross join (
  select 'f' as g union all select 'm'
) G

go

declare @m varchar(8000)
declare @f varchar(8000)
set @m = '' set @f = ''
select
  @m = case when gender = 'f' then @m else @m + name end,
  @f = case when gender = 'm' then @f else @f + name end
from #malefemale

select
  substring(case when g ='m' then @m else @f end,p,10) as name,
  g as gender
from GenderOut
where p <= datalength(@m) + datalength(@f)
and pm <= datalength(@m)
and pf <= datalength(@f)
order by p, g
go

drop table #malefemale, GenderOut

Steve

Adam Machanic wrote:

>"Steve Kass" <skass@drew.edu> wrote in message
>news:418C29D9.7020607@drew.edu...
>
>
>>This isn't simpler, but it's likely to be more efficient. Until SQL
>>
>>
>
> No, it's about 20x more complex ... I'm going to go stare at it for a
>while and try to understand the logic :-)
>
>
>
>