Query

I have a table similar to the one given below… where col1 is an identity column and other columns are of integer type…

Col1Col2Col3
11020
21121
31222
41323
51424
61525
71626
81727
91828
101929


I need the following output in a single query…


Col1Col2Col3
11021
31223
51425
71627
91829


and the condition is i have to use this table only once in my query and should not use joins. any guesses???

Thanks,
Ram

"It is easy to write code for a spec and walk in water, provided, both are freezed..."

"and the condition is i have to use this table only once in my query and should not use joins"
Is this some sort of quiz ? Any prize for getting the right query ?


KH

The resultset must have the first row's value for col2 and the second row's value for col3, third row's value for col2 and fourth rows's for col3 from the source table and so on...the identity column (col1) is displayed as it is.<br /><br />my friend asked me to do this. i tried and couldnt find an answer. neither i know the intention of this query. may be it was his homework <img src='/community/emoticons/emotion-1.gif' alt='' /><br /><br />below is the query i gave to him...<br /><br />SELECT a.Col1,a.Col2,b.Col3 <br /> FROM Table1 a<br /> JOIN Table1 b <br /> ON a.Col1+1 = b.Col1<br /> WHERE a.Col1%2 &lt;&gt; 0<br /><br />but he needs to do this without using join.<br /><br />Thanks,<br />Ram<br /><br />"It is easy to write code for a spec and walk in water, provided, both are freezed..."

<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by khtan</i><br /><br /><i>"and the condition is i have to use this table only once in my query and should not use joins"</i><br />Is this some sort of quiz ? Any prize for getting the right query ?<br /><br /><hr noshade size="1"><br /><font size="1">KH</font id="size1"><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br /><br />may be if i get a share. i would give you some... [<img src='/community/emoticons/emotion-2.gif' alt='' />]<br /><br />Thanks,<br />Ram<br /><br />"It is easy to write code for a spec and walk in water, provided, both are freezed..."

how about subquery ? is it allowed ? [<img src='/community/emoticons/emotion-2.gif' alt='' />]<br /><br /><hr noshade size="1"><br /><font size="1">KH</font id="size1">

conditions are use only one query and use the table only once <img src='/community/emoticons/emotion-2.gif' alt='' /><br /><br />Thanks,<br />Ram<br /><br />"It is easy to write code for a spec and walk in water, provided, both are freezed..."

Of course you can use a correlated subquery as an expression in the SELECT clause ...

SELECT a.Col1,a.Col2,
(SELECT b.Col3 FROM Table1 b WHERE a.Col1+1 = b.Col1) Col3
FROM Table1 a
WHERE a.Col1%2 <> 0

... but it's basically the same thing as using a JOIN. The version with the JOIN may be somewhat faster (not necessarily).

The JOIN syntax should be used wherever it's appropriate, so why bother?

quote:Originally posted by Adriaan

Of course you can use a correlated subquery as an expression in the SELECT clause ...

SELECT a.Col1,a.Col2,
(SELECT b.Col3 FROM Table1 b WHERE a.Col1+1 = b.Col1) Col3
FROM Table1 a
WHERE a.Col1%2 <> 0

... but it's basically the same thing as using a JOIN. The version with the JOIN may be somewhat faster (not necessarily).

The JOIN syntax should be used wherever it's appropriate, so why bother?

we have to use the table only once...

Thanks,
Ram

"It is easy to write code for a spec and walk in water, provided, both are freezed..."

<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Madhivanan</i><br /><br />&gt;&gt;and the condition is i have to use this table only once in my query and should not use joins. any guesses???<br /><br />Who gives this question?<br /><br />Without any Logic ([<img src='/community/emoticons/emotion-2.gif' alt='' />]), you can try this<br /><br />select col1 as col1, (col1+9) as col2, (col1+20) as col3 from yourTable<br />where col1 % 2=1<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />It was posted in my organization forum. i was so curious to know the answer instead of knowing the owner's name[<img src='/community/emoticons/emotion-1.gif' alt='' />]<br /><br />also, the data which i gave, was a sample data...<br /><br />Thanks,<br />Ram<br /><br />"It is easy to write code for a spec and walk in water, provided, both are freezed..."

Technically speaking, you can't return data from different rows of the same table, on a single row in the resultset, unless you use a subquery or a self-join.

You might try a UDF to cover up the subquery, but that would be cheating.

To get away with only using the table once, although it will give you more overhead you could insert the data into a temp table and then use that one twice.

SELECT * -- lazy coding
INTO #table1 -- yes, yes lazy coding
FROM t

And then use you query on your temporary table

SELECT a.Col1,a.Col2,b.Col3
FROM Table1 a
JOIN Table1 b
ON a.Col1+1 = b.Col1
WHERE a.Col1%2 <> 0

Or insert it into a table variable depending on you table's size and available RAM


Regards,

Robert.

In reading the desired results, I think they are just looking for
select Col1, Col2, Col3+1
from table
WHERE Col1%2 <> 0

We have a winner!

Will this do ?


declare @table table
(
Col1 int,
Col2 int,
Col3int
)
insert into @table
select 1, 10, 20 union all
select 2, 11, 21 union all
select 3, 12, 22 union all
select 4, 13, 23 union all
select 5, 14, 24 union all
select 6, 15, 25 union all
select 7, 16, 26 union all
select 8, 17, 27 union all
select 9, 18, 28 union all
select 10, 19, 29


selectCol1 + (Col1 % 2) - 1 as Col1, sum(Col2) as Col2, sum(Col3) as Col3
from
(
selectCol1,
case when Col1 % 2 = 1 then Col2 else 0 end as Col2,
case when Col1 % 2 = 0 then Col3 else 0 end as Col3
from@table
) a
group by Col1 + (Col1 % 2) - 1

/* RESULT :
Col1 Col2 Col3
----------- ----------- -----------
1 10 21
3 12 23
5 14 25
7 16 27
9 18 29

(5 row(s) affected)
*/


KH

Thats great work Tan. And, i have got one more solution and it looks even simpler...

SELECT min(Col1) AS Col1
,min(Col2) AS Col2
,max(Col3) AS Col3
FROM Table1
GROUP BY (Col1 + 1)/2

Thanks,
Ram

"It is easy to write code for a spec and walk in water, provided, both are freezed..."

quote:Originally posted by ramkumar.mu

Thats great work Tan. And, i have got one more solution and it looks even simpler...

SELECT min(Col1) AS Col1
,min(Col2) AS Col2
,max(Col3) AS Col3
FROM Table1
GROUP BY (Col1 + 1)/2

Thanks,
Ram

"It is easy to write code for a spec and walk in water, provided, both are freezed..."

This query only works for that set of sample data. Unless the requirement for the query is as what you demonstrated there. min of Col2 and max of Col3


KH

Indeed, likewise Druer's solution only works with the specific set of data.

Yes! that was a smart work. Thanks guys and a special thanks for Tan [<img src='/community/emoticons/emotion-2.gif' alt='' />]<br /><br />Thanks,<br />Ram<br /><br />"It is easy to write code for a spec and walk in water, provided, both are freezed..."