Here you go NepaliBhai...................... create table #table1(steps int) insert into #table1(steps)values(1) insert into #table1(steps)values(3) insert into #table1(steps)values(4) insert into #table1(steps)values(5) select distinct x.stepFrom, Y.stepTo from (select rank()over(order by a.steps )as rank,a.steps as stepFrom from (select top 3 * from #table1 order by 1 asc) a left outer join #table1 b on a.steps =b.steps) x inner join (select rank()over(order by a.steps )as rank,a.steps as stepTo from (select top 3 * from #table1 order by 1 desc) a left join #table1 b on a.steps =b.steps) y on x.rank =y.rank drop table #table1

SQL_PRO · Aug 27, 2014 11:35 AM

SQL_PRO, i don't think you should be using rank function. Can you try using only ANSI SQL :)? Btw, if you are using sql 2012 this thing can be done with ease Select * from (select steps as StepsFrom,lead(steps) over (order by steps) as StepsTo from #table1 ) t1 where StepsTo is not null But ANSI SQL would be more interesting :)

virusno1 · Aug 27, 2014 11:48 AM

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

NepaliBhai · Aug 27, 2014 1:08 PM

Here is another puzzle. create table #table1(steps int) insert into #table1(steps)values(1) insert into #table1(steps)values(3) insert into #table1(steps)values(4) insert into #table1(steps)values(5) Now generate random rows for these rows. e.g 1 3, 5, 4 or 5, 3, 1, 4 Remember, you need to produce random rows every time you run it.

virusno1 · Aug 27, 2014 2:17 PM

I like your idea. It will be fun to work on. I will try to come up with syntax.

NepaliBhai · Aug 27, 2014 2:41 PM

Here it is. create table #table1(steps int) insert into #table1(steps)values(1) insert into #table1(steps)values(3) insert into #table1(steps)values(4) insert into #table1(steps)values(5) SELECT cast(steps as varchar) + ',' FROM #table1 ORDER BY NEWID() drop table #table1

NepaliBhai · Aug 28, 2014 7:43 AM