SQL Server Performance

Query

Discussion in 'General Developer Questions' started by ramkumar.mu, Apr 19, 2006.

  1. ramkumar.mu New Member

    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..."
  2. FrankKalis Moderator

    I don't understand the logic you want to apply here!<br />Either you have typos in the desired resultset or you need to explain that resultset a bit more precisely. I would have guessed something like<br /><pre id="code"><font face="courier" size="2" id="code"><br />WHERE identitycolumn % 2 &lt;&gt; 0<br /></font id="code"></pre id="code"><br />but that doesn't deliver your Col3 values.<br /><br />Btw, is this a homework assignment for your? [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)
  3. khtan New Member

    "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
  4. ramkumar.mu New Member

    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..."
  5. ramkumar.mu New Member

    <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=':D' />]<br /><br />Thanks,<br />Ram<br /><br />"It is easy to write code for a spec and walk in water, provided, both are freezed..."
  6. FrankKalis Moderator

    Tell your friend to use a cursor. [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)
  7. khtan New Member

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

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

    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?
  10. Madhivanan Moderator

    &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=':D' />]), 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
  11. ramkumar.mu New Member

    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..."
  12. ramkumar.mu New Member

    <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=':D' />]), 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..."
  13. Adriaan New Member

    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.
  14. RGKN New Member

    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.
  15. Madhivanan Moderator

    &gt;&gt;It was posted in my organization forum<br /><br />Let us know if you get answer there [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  16. druer New Member

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

    We have a winner!
  18. khtan New Member

    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
  19. Madhivanan Moderator

    Thats cool Tan. You play very well with SQL [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  20. ramkumar.mu New Member

    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..."
  21. khtan New Member

    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
  22. RGKN New Member


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



  23. Madhivanan Moderator

    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..."
    As said, test with different data. Tan's code is the way to go

    Madhivanan

    Failing to plan is Planning to fail
  24. ramkumar.mu New Member

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

Share This Page