-
Nov 9th, 2008, 09:33 PM
#1
Thread Starter
Hyperactive Member
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
-
Nov 10th, 2008, 05:41 AM
#2
Re: exclude zero
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
-
Nov 10th, 2008, 06:41 AM
#3
Re: exclude zero
Thread moved to Database Development forum - which is where SQL questions belong
-
Nov 10th, 2008, 07:38 AM
#4
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
-
Nov 11th, 2008, 12:03 AM
#5
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;
-
Nov 11th, 2008, 03:36 AM
#6
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.
-
Nov 11th, 2008, 05:43 AM
#7
Re: exclude zero
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?
-
Nov 11th, 2008, 06:57 AM
#8
Re: exclude zero
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
-
Nov 13th, 2008, 12:37 AM
#9
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.
-
Nov 13th, 2008, 05:28 AM
#10
Re: exclude zero
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|