exclude zero

[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

[TysonLPrice]

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:

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

[si_the_geek]

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

[FunkyDexter]

I think you'd need a pair of sub-selects for that:-

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

[anhn]

Another way:

SELECT Min(IIf(col1<>0,col1,Null)) AS Min1, Min(IIf(col2<>0,col2,Null)) AS Min2 
FROM Table1;

[leinad31]

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.

[TysonLPrice]

Another way:

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?

[FunkyDexter]

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.

[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.

[TysonLPrice]

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.