SELECT NUMBER FROM MASTER..SPT_VALUES S LEFT JOIN A ON S.NUMBER=A.A1 WHERE S.NUMBER>=(SELECT MIN(A1) FROM A) AND S.NUMBER<=(SELECT MAX(A1) FROM A) AND S.TYPE='P' AND A.A1 IS NULL
--sql 2000 select m.px from ( select t.* , px = (select count(1) from a where a1 < t.a1) + 1 from a t ) m, ( select t.* , px = (select count(1) from a where a1 < t.a1) + 1 from a t ) n where m.a1 = n.a1 - 1 and m.px <> n.px - 1--sql 2005 select m.px from ( select t.* , px = row_number() over(order by a1) from a t ) m, ( select t.* , px = row_number() over(order by a1) from a t ) n where m.a1 = n.a1 - 1 and m.px <> n.px - 1
FROM MASTER..SPT_VALUES S
LEFT JOIN A ON S.NUMBER=A.A1
WHERE S.NUMBER>=(SELECT MIN(A1) FROM A)
AND S.NUMBER<=(SELECT MAX(A1) FROM A)
AND S.TYPE='P'
AND A.A1 IS NULL
select m.px from
(
select t.* , px = (select count(1) from a where a1 < t.a1) + 1 from a t
) m,
(
select t.* , px = (select count(1) from a where a1 < t.a1) + 1 from a t
) n
where m.a1 = n.a1 - 1 and m.px <> n.px - 1--sql 2005
select m.px from
(
select t.* , px = row_number() over(order by a1) from a t
) m,
(
select t.* , px = row_number() over(order by a1) from a t
) n
where m.a1 = n.a1 - 1 and m.px <> n.px - 1