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

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

---

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

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)

---

Another way:

Code:

---

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

---

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.

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?

Quote:

---

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.

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.

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. :)