Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2009
    Posts
    4

    Unanswered: a trick for something special

    Hello,

    i need a interesting select but i don't know exactly how to do it in MS SQL.

    table like that:

    1 | 111111 | 0 | 0 | 0 | 0 |
    1 | 0 | 0 | 0 | 222222 | 0 |
    1 | 0 | 333333 | 0 |0 | 0 |
    1 | 0 | 0 | 444444 |0 | 0 |
    1 | 0 | 0 | 0 |0 | 555555 |

    and i need a single row like this

    1 | 111111 | 333333 | 444444 | 222222 | 555555 |

    Can you help me guys?

    Thank you!

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Code:
    select max(col1), max(col2), max(col3), ...
    FROM mytable;
    Are you sure that there will only be 1 non-zero value per column?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT SUM(col1), SUM(col2), SUM(col3), ...
      FROM daTable
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Feb 2009
    Posts
    4

    Best Regards

    Thank you guys.

    Actually Max did the trick...but i have to test it on a big database to see if it really works as expected..

    Because that 11111 are Ms SQL Date format as string..

    Anyway..

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by w3bbuilder
    Anyway..
    yeah, anyway, thanks for tricking us

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2009
    Posts
    4
    Quote Originally Posted by Wim
    Code:
    select max(col1), max(col2), max(col3), ...
    FROM mytable;
    Are you sure that there will only be 1 non-zero value per column?
    I have more than one records different by 0 on the column but i only want the first occurence. So i sorted before by date and max() tooked the first.

    Quote Originally Posted by r937
    yeah, anyway, thanks for tricking us

    R937 i don't understand your point.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Max did not take the first, it took the highest value. If you want the first occurance then Min() will give you the result.
    George
    Home | Blog

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the point that you did not understand is that you showed us a bunch of numbers which actually looked like, um, numbers...

    and my solution fit your data perfectly...

    and then you said they weren't really numbers after all, but dates...

    and of course everyone knows you can't add dates, that wouldn't make sense...

    so you tricked us

    that's my point
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2009
    Posts
    4

    :-(

    actually they where dates in unixtime format and i wanted to help you understand better.

    i now how to manage a lot of things in almoast every database engines so i didn't want to bother you with stuff like formats and other things...

    it was just a simple question regarding a simple trick i couldn't get. usually, as a programmers we have some good or bad concentration levels and it's better to ask before losing a lot of time thinking.

    your advices were very useful for me. thank you again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •