Build this query guys(Its a tough one)

[chakuli]

Say the table "TblTest" contains a single column with the following data (3,6,4,8,32,26). Write an SQL statement to display the data in the following format

col1 col2
---- -----
3 6
6 4
4 8
8 32
32 26

No Ordering of data allowed. Goes without saying NO CURSORS.

 

[tlbroadbent]

Is this a test? Or a homework assignment? Why don't you provide an answer? Terry L. Broadbent - Salt Lake City, UT
Home of the 2002 Winter Olympics (Feb 8-24)

http://www.saltlake2002.com

 

[Rosko]

I agree Terry, Is it really something you want an answer to, chakuli ,or are you trying to test us?

You could have posted it as a helpful tip.

Rosko

 

[Guest_imported]

Hey guys,
This is a question put to me in an Interview. I have no clue about the answer. Give it a try.

 

[tlbroadbent]

Here is one T-SQL solution.

Create Table #r (ID int, RecID int identity)
Set nocount on
insert #r Select ID From MyTable
Set nocount off

Select ID1=q1.ID, ID2=q2.ID From
(Select ID, RecID From #r Where RecID % 2 = 1) q1
Inner Join
(Select ID, RecID From #r Where RecID % 2 = 0) q2
On q1.RecID+1=q2.RecID
Terry L. Broadbent - Salt Lake City, UT
Home of the 2002 Winter Olympics (Feb 8-24)

http://www.saltlake2002.com

 

[bperry]

chakuli:
I am thinking this is impossible without either (a) using two columns, incl one unique column, as tbroadbent has done, or else (b) building a temporart table to help construct the solution.

But your original criteria indicates (a) one column only, and (b) a single SQL statement only, which implies no temporary table.

I don't see how this can be done? Anyone else?
bperry

 

[bperry]

A clarification on my earlier post:

This would be quite easy if the column *increased* in value (say, 3,4,6,8,26,23). For example, this would to it:

Select Value as Col1,
(Select TOP 1 Value
From tblTest t2
Where t2.Value > t1.Value) as Col2

From tblTest t1

However, because we can't depend on the values increasing, and we can't use other tricks like another (identity) column or a temporary table, I can't see a way of doing this in a single SQL statement. Anyone else?
b perry

 

[DBCustard]

I agree, I think the only way to solve it would be to come up with an expression to relate col2 to col1, if you plot them against eachother it seems to be a relatively smooth curve, so this might be possible, then you could rely on a cartesian product ...

select * from tblTest col1, tblTest col2
where col1.col [someexpression] col2.col

Any mathematicians out there?