Posted by: NepaliBhai August 27, 2014
SQL Select Statement
Login in to Rate this Post:     0       ?        

SQL_PRO Dude,
It doesn't work that way. You can't hard code like top 3. It should be dynamic.

What if the data is like this.
insert into #table1(steps)values(1)
insert into #table1(steps)values(2)
insert into #table1(steps)values(3)
insert into #table1(steps)values(4)
insert into #table1(steps)values(5)
insert into #table1(steps)values(7)
insert into #table1(steps)values(9)
insert into #table1(steps)values(10)
insert into #table1(steps)values(17)
insert into #table1(steps)values(31)

So the solution is like this

go
WITH TEMP_CTE AS (
SELECT steps, ROW_NUMBER() OVER(ORDER BY steps) AS ROW_NUM FROM #table1
)
SELECT t1.steps, t2.steps FROM TEMP_CTE t1, TEMP_CTE t2
WHERE t1.ROW_NUM < (SELECT MAX(ROW_NUM) FROM TEMP_CTE)
AND (t2.ROW_NUM - 1) = t1.ROW_NUM
Read Full Discussion Thread for this article