row_num     v1          v2          v3          v4          v5
----------- ----------- ----------- ----------- ----------- -----------
1           50          55          60          90          150
2           50          60          49          70          89

row_num     nbr         v
----------- ----------- -----------
1           1           50
1           2           55
1           3           60
1           4           90
1           5           150

DECLARE	@Sample TABLE
	(
		row_num INT,
		nbr INT,
		v INT
	)

INSERT	@Sample
VALUES	(1, 1,  50),
	(1, 2,  55),
	(1, 3,  60),
	(1, 4,  90),
	(1, 5, 150)
;WITH cte
AS (
	SELECT	row_num,
		ROW_NUMBER() OVER (PARTITION BY row_num ORDER BY v) - nbr AS Yak
	FROM	@Sample
)
SELECT		row_num
FROM		cte
GROUP BY	row_num
HAVING		MAX(Yak) > 0

SELECT row_num, v1, v2, v3, v4, v5
FROM (SELECT row_num, 'v'+CAST(nbr AS VARCHAR) n, v FROM @Sample) a
PIVOT(MAX(v) FOR n IN (v1,v2,v3,v4,v5)) b
WHERE v1 < v2 AND v2 < v3 AND v3 < v4 AND v4 < v5

;WITH cte
AS (
	SELECT	row_num,
		     RANK() OVER (PARTITION BY row_num ORDER BY v) - nbr AS Yak
	FROM	@Sample
)
SELECT		row_num
FROM		cte
GROUP BY	row_num
HAVING		MAX(Yak)= 0 AND MIN(Yak) = 0

select a.row_num,
       a.v            as v1,
       b.v            as v2,
       c.v            as v3,
       d.v            as v4,
       e.v            as v5
from tableName a
join tableName b on b.row_num = a.row_num and a.v < b.v
join tableName c on c.row_num = b.row_num and b.v < c.v
join tableName d on d.row_num = c.row_num and c.v < d.v
join tableName e on e.row_num = d.row_num and d.v < e.v

quote:

---

Originally posted by mmarovic

I am not sure about performance, but next method looks simpler to me:

select a.row_num,
       a.v            as v1,
       b.v            as v2,
       c.v            as v3,
       d.v            as v4,
       e.v            as v5
from tableName a
join tableName b on b.row_num = a.row_num and a.v < b.v and a.nbr = 1 and b.nbr = 2
join tableName c on c.row_num = b.row_num and b.v < c.v and c.nbr = 3
join tableName d on d.row_num = c.row_num and c.v < d.v and d.nbr = 4
join tableName e on e.row_num = d.row_num and d.v < e.v and e.nbr = 5

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

---

CREATE UNIQUE CLUSTERED INDEX UCX_Sample ON #Sample (ColNum, RowNum)

quote:

---

SELECT a.RowNum
FROM #Sample AS a
INNER JOIN #Sample AS b ON b.RowNum = a.RowNum
AND b.ColNum = 2
AND b.Value > a.Value
INNER JOIN #Sample AS c ON c.RowNum = b.RowNum
AND c.ColNum = 3
AND c.Value > b.Value
INNER JOIN #Sample AS d ON d.RowNum = c.RowNum
AND d.ColNum = 4
AND d.Value > c.Value
INNER JOIN #Sample AS e ON e.RowNum = d.RowNum
AND e.ColNum = 5
AND e.Value > d.Value
INNER JOIN #Sample AS f ON f.RowNum = e.RowNum
AND f.ColNum = 6
AND f.Value > e.Value
WHERE a.ColNum = 1

---

quote:

---

Originally posted by mmarovic

.....
However, with index starting with colNum, I expect logical reads to drop.
....

---

quote:

---

Originally posted by sunitabeck

quote:

---

Originally posted by mmarovic

.....
However, with index starting with colNum, I expect logical reads to drop.
....

---

How do you just LOOK at some code and say "this would have fewer logical reads", or "that would be more efficient"!?! I admit that don't know even the very the basics of query optimization - can you point me to some books/articles that would help me get a working knowledge?

---