SQLTeam.com | Weblogs | Forums

I need to change don't always want 2 rows returned

sql2008r2

#1

I need to make a small adjustment here and not sure if it can be
done.
In the following, if there is no '001' row, then I only want 1 row for
that order, but when there is 001 i do want that and the next one
according to earliest time.

CREATE VIEW rklib.clspaytp AS Select * from
(
Select x.*,
row_number() over (partition by otord#
order by case ottrnc when '001' then 1 else 2 end
, ottrnd, ottrt
)
as RowN
from rklib.clspaytpp x
) a
where a.RowN in (1,2)


#2
; with cte as
(
	Select 	x.*,
		row_number() over ( partition by otord# order by case ottrnc when '001' then 1 else 2 end, ottrnd, ottrt ) as RowN
	from 	rklib.clspaytpp x
),
cte1 as
(
	Select 	* 
	from	cte
	where	RowN	= 1
) 
select	*
from	cte1
union all
select	*
from	cte c
where 	RowN 	 = 2
and	not exists
	(
		select	*
		from	cte1 x
		where	x.otord#	= c.otord#
		and	x.ottrnc	= '001'
	)

#3

Wy not use the OFFSET function?