exclude zero-VBForums
Results 1 to 10 of 10

Thread: exclude zero

  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
    PowerPoster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    2,862

    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
    39,092

    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
    6,495

    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)
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - 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
    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.

  7. #7
    PowerPoster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    2,862

    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?

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

    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.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

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

  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
    PowerPoster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    2,862

    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
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.