Results 1 to 10 of 10

Thread: exclude zero

Hybrid View

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2006
    Location
    /root/usr/local/bin
    Posts
    476

    exclude zero

    hello!
    how would i exclude zero pls see sample below:
    col1 - col2
    0 - 1
    0 - 3
    1 - 2
    2 - 2
    3 - 3
    4 - 4

    select min(col1),min(col2) from table where col1<>0;

    results:
    1-2 supposedly it is 1-1
    *****************
    VB6,PHP,VS 2005

  2. #2
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: exclude zero

    Quote Originally Posted by basti42
    hello!
    how would i exclude zero pls see sample below:
    col1 - col2
    0 - 1
    0 - 3
    1 - 2
    2 - 2
    3 - 3
    4 - 4

    select min(col1),min(col2) from table where col1<>0;

    results:
    1-2 supposedly it is 1-1
    SQL 2000 - This is just based on what you posted:

    Code:
    create table #testIt(col1 int,col2 int)
    insert into #TestIt(col1,col2) values(0,1)
    insert into #TestIt(col1,col2) values(0,3)
    insert into #TestIt(col1,col2) values(1,2)
    insert into #TestIt(col1,col2) values(2,2)
    insert into #TestIt(col1,col2) values(3,3)
    insert into #TestIt(col1,col2) values(4,4)
    
    select min(t1.col1),min(t2.col2)
    from #TestIt t1, #TestIt t2
    where t1.col1<>0
    and t1.col1<>0
    
    drop table #testIt

  3. #3
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: exclude zero

    Thread moved to Database Development forum - which is where SQL questions belong

  4. #4
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: exclude zero

    I think you'd need a pair of sub-selects for that:-
    Code:
    Select
       (Select min(col1) from table where col1 <> 0),
       (Select min(col2) from table where col2 <> 0)
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  5. #5
    Head Hunted anhn's Avatar
    Join Date
    Aug 2007
    Location
    Australia
    Posts
    3,669

    Re: exclude zero

    Another way:
    Code:
    SELECT Min(IIf(col1<>0,col1,Null)) AS Min1, Min(IIf(col2<>0,col2,Null)) AS Min2 
    FROM Table1;
    • Don't forget to use [CODE]your code here[/CODE] when posting code
    • If your question was answered please use Thread Tools to mark your thread [RESOLVED]
    • Don't forget to RATE helpful posts

    • Baby Steps a guided tour
    • IsDigits() and IsNumber() functions • Wichmann-Hill Random() function • >> and << functions for VB • CopyFileByChunk

  6. #6
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: exclude zero

    Quote Originally Posted by anhn
    Another way:
    Code:
    SELECT Min(IIf(col1<>0,col1,Null)) AS Min1, Min(IIf(col2<>0,col2,Null)) AS Min2 
    FROM Table1;
    The OP never said what DB was being used. This won't sysntax check on SQL 2000. Is that access?

  7. #7
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: exclude zero

    Is that access?
    Yes but you could achieve the same thing using case in sqlserver. Either way though, I'd recommend the query I posted instead. It's more readable and it should perform better too.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  8. #8
    PowerPoster
    Join Date
    Nov 2002
    Location
    Manila
    Posts
    7,629

    Re: exclude zero

    In terms of SQL tuning/optimization....

    Post #2: Additional overhead from table join (intermediate table).
    Post #5: Additional overhead from full table scan. All records (or all index entries whichever is applicable) have to be retrieved, checked, and possibly column value converted to NULL.
    Post #4: I would recommend this query if an index exists on relevant columns since then only the index is scanned (less disk I/O, and b-tree or r-tree scanning of index).

    If relevant index exists, preference is post 4, 2, 5. If no index exists (you will end up doing a full table scan whatever the query), preference is post 5, 4, 2.

  9. #9
    PowerPoster
    Join Date
    Nov 2002
    Location
    Manila
    Posts
    7,629

    Re: exclude zero

    Performance varies depending on how database, tables, indexes, and the data itself. There is no such thing as a one size fits all approach to writing efficient SQL.

  10. #10
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: exclude zero

    Quote Originally Posted by leinad31
    Performance varies depending on how database, tables, indexes, and the data itself. There is no such thing as a one size fits all approach to writing efficient SQL.
    True...plus the compiler, at least SQL 2005, sometimes has a mind of it's own.

Posting Permissions

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



Click Here to Expand Forum to Full Width