|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Aaron, Itzik - about the MIN FunctionCol1 Col2 344 54353 0 5345345 233 12 543 435 13 0 my ultimate Final result should "12" which is minimum of both the columns. (and non-zero) (i didnt know how to get that so, i was taking min of each column (in this case 0,0) and then sending it to another function which will give me the min of the result (0) But i dont want 0's to be considered. - That is where i am stuck. Hope i explained the situation correctly. Thanks guys. Hope you don´t bother that I answer you :-)
Select CASE WHEN min(col1) < min(col2) THEN min(col1) ELSE min(col2) END AS Minimun from sometable Where col1 <> 0 OR col2 <> 0 HTH, Jens Suessmeyer. --- http://www.sqlserver2005.de --- Show quote "RP" <R*@discussions.microsoft.com> wrote in message news:CB269A67-F5BF-4D63-9BC5-D70A4109DA9E@microsoft.com... >I will be clear this time. This is what i want. > > Col1 Col2 > 344 54353 > 0 5345345 > 233 12 > 543 435 > 13 0 > > my ultimate Final result should "12" which is minimum of both the > columns. > (and non-zero) > > (i didnt know how to get that so, i was taking min of each column (in this > case 0,0) and then sending it to another function which will give me the > min > of the result (0) > But i dont want 0's to be considered. - That is where i am stuck. > > Hope i explained the situation correctly. > > Thanks guys. I get 0 from this (due to the OR in the where clause). Of course you can't
use AND, because the lowest non-zero value might appear in one column where the other column is 0... Show quote > Hope you don´t bother that I answer you :-) > > Select > CASE > WHEN min(col1) < min(col2) > THEN min(col1) > ELSE min(col2) END > AS Minimun > from sometable > Where col1 <> 0 OR col2 <> 0 Won't that give him 0 also?
How about: Select Case When Min1 < Min2 Then Min1 else Min2 End From (Select Min(Col1) AS Min1 From SomeTable WHERE Col1 <> 0) T1 Inner Join (Select Min(Col2) AS Min2 From SomeTable WHERE Col2 <> 0) T2 ON 1=1 hth, -- Show quoteDaniel Wilson Senior Software Solutions Developer Embtrak Development Team http://www.Embtrak.com DVBrown Company "Jens Süßmeyer" <Jens@remove_this_for_contacting_sqlserver2005.de> wrote in message news:OYKSvnwgFHA.1372@TK2MSFTNGP10.phx.gbl... > Hope you don´t bother that I answer you :-) > > Select > CASE > WHEN min(col1) < min(col2) > THEN min(col1) > ELSE min(col2) END > AS Minimun > from sometable > Where col1 <> 0 OR col2 <> 0 > > HTH, Jens Suessmeyer. > > --- > http://www.sqlserver2005.de > --- > > > "RP" <R*@discussions.microsoft.com> wrote in message > news:CB269A67-F5BF-4D63-9BC5-D70A4109DA9E@microsoft.com... > >I will be clear this time. This is what i want. > > > > Col1 Col2 > > 344 54353 > > 0 5345345 > > 233 12 > > 543 435 > > 13 0 > > > > my ultimate Final result should "12" which is minimum of both the > > columns. > > (and non-zero) > > > > (i didnt know how to get that so, i was taking min of each column (in this > > case 0,0) and then sending it to another function which will give me the > > min > > of the result (0) > > But i dont want 0's to be considered. - That is where i am stuck. > > > > Hope i explained the situation correctly. > > > > Thanks guys. > > select min(x)
from ( select min(col1) as x from [table] where col1 <> 0 union all select min(col2) as x from [table] where col2 <> 0 ) AS derived Show quote "RP" wrote: > I will be clear this time. This is what i want. > > Col1 Col2 > 344 54353 > 0 5345345 > 233 12 > 543 435 > 13 0 > > my ultimate Final result should "12" which is minimum of both the columns. > (and non-zero) > > (i didnt know how to get that so, i was taking min of each column (in this > case 0,0) and then sending it to another function which will give me the min > of the result (0) > But i dont want 0's to be considered. - That is where i am stuck. > > Hope i explained the situation correctly. > > Thanks guys. CREATE TABLE blat
( col1 BIGINT, col2 BIGINT ) GO INSERT blat SELECT 344,54353 INSERT blat SELECT 0,5345345 INSERT blat SELECT 233,12 INSERT blat SELECT 543,435 INSERT blat SELECT 13,0 GO SELECT MIN(bothcols) FROM ( SELECT bothcols = MIN(col1) FROM blat WHERE col1>0 UNION ALL SELECT bothcols = MIN(col2) FROM blat WHERE col2>0 ) x GO DROP TABLE blat Show quote "RP" <R*@discussions.microsoft.com> wrote in message news:CB269A67-F5BF-4D63-9BC5-D70A4109DA9E@microsoft.com... >I will be clear this time. This is what i want. > > Col1 Col2 > 344 54353 > 0 5345345 > 233 12 > 543 435 > 13 0 > > my ultimate Final result should "12" which is minimum of both the > columns. > (and non-zero) > > (i didnt know how to get that so, i was taking min of each column (in this > case 0,0) and then sending it to another function which will give me the > min > of the result (0) > But i dont want 0's to be considered. - That is where i am stuck. > > Hope i explained the situation correctly. > > Thanks guys. Or,
select ((mn1+mn2) - abs(mn1-mn2))/2 as mn from (select min(nullif(col1, 0)) as mn1, min(nullif(col2, 0)) as mn2 from blat) as d Just for fun. ;-) Show quote "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:uytfTswgFHA.576@TK2MSFTNGP15.phx.gbl... > CREATE TABLE blat > ( > col1 BIGINT, > col2 BIGINT > ) > GO > > INSERT blat SELECT 344,54353 > INSERT blat SELECT 0,5345345 > INSERT blat SELECT 233,12 > INSERT blat SELECT 543,435 > INSERT blat SELECT 13,0 > > GO > > SELECT MIN(bothcols) > FROM > ( > SELECT bothcols = MIN(col1) FROM blat WHERE col1>0 > UNION ALL > SELECT bothcols = MIN(col2) FROM blat WHERE col2>0 > ) x > GO > > DROP TABLE blat > > > > > > "RP" <R*@discussions.microsoft.com> wrote in message > news:CB269A67-F5BF-4D63-9BC5-D70A4109DA9E@microsoft.com... >>I will be clear this time. This is what i want. >> >> Col1 Col2 >> 344 54353 >> 0 5345345 >> 233 12 >> 543 435 >> 13 0 >> >> my ultimate Final result should "12" which is minimum of both the >> columns. >> (and non-zero) >> >> (i didnt know how to get that so, i was taking min of each column (in >> this >> case 0,0) and then sending it to another function which will give me the >> min >> of the result (0) >> But i dont want 0's to be considered. - That is where i am stuck. >> >> Hope i explained the situation correctly. >> >> Thanks guys. > > CASE should work fine here.
SELECT CASE WHEN MIN(col1) <= MIN(col2) THEN MIN(col1) ELSE MIN(col2) END AS smallest_foobar FROM Blat WHERE 0 NOT IN(col1, col2); CASE should work fine here. Given data:
Col1 Col2 344 54353 0 5345345 233 12 543 435 3 0 Won't your solution return 12, not 3? Very simple & elegant though ... I often forget a WHERE <constant> [NOT] IN (<fieldname> [,<fieldname>] ...n) construct. -- Show quoteDaniel Wilson Senior Software Solutions Developer Embtrak Development Team http://www.Embtrak.com DVBrown Company "--CELKO--" <jcelko***@earthlink.net> wrote in message news:1120752029.218813.119130@z14g2000cwz.googlegroups.com... > CASE should work fine here. > > SELECT CASE WHEN MIN(col1) <= MIN(col2) > THEN MIN(col1) ELSE MIN(col2) END AS smallest_foobar > FROM Blat > WHERE 0 NOT IN(col1, col2); > > CASE should work fine here. > RP wrote:
Show quote > I will be clear this time. This is what i want. Select TOP 1 *> > Col1 Col2 > 344 54353 > 0 5345345 > 233 12 > 543 435 > 13 0 > > my ultimate Final result should "12" which is minimum of both the > columns. (and non-zero) > > (i didnt know how to get that so, i was taking min of each column (in > this case 0,0) and then sending it to another function which will > give me the min of the result (0) > But i dont want 0's to be considered. - That is where i am stuck. > > Hope i explained the situation correctly. > > Thanks guys. From ( Select MIN(Col1) From MyTable UNION ALL Select MIN(Col2) From MyTable Order By 1 ) a David, I get:
Server: Msg 1033, Level 15, State 1, Line 9 The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified. And when I fix that, I get: Server: Msg 8155, Level 16, State 2, Line 2 No column was specified for column 1 of 'a'. And when I fix that, I get a result of 0, which is not what the OP wanted... A Show quote "David Gugick" <david.gugick-nospam@quest.com> wrote in message news:%23k329u4gFHA.2152@TK2MSFTNGP14.phx.gbl...RP wrote: > Select TOP 1 * > From ( > Select MIN(Col1) > From MyTable > UNION ALL > Select MIN(Col2) > From MyTable > Order By 1 ) a Aaron Bertrand [SQL Server MVP] wrote:
Show quote > David, I get: Serves me right for not testing.> > Server: Msg 1033, Level 15, State 1, Line 9 > The ORDER BY clause is invalid in views, inline functions, derived > tables, and subqueries, unless TOP is also specified. > > And when I fix that, I get: > > Server: Msg 8155, Level 16, State 2, Line 2 > No column was specified for column 1 of 'a'. > > And when I fix that, I get a result of 0, which is not what the OP > wanted... > > A > create table #test(col1 int, col2 int) insert into #test values (344, 54353) insert into #test values (0 ,5345345) insert into #test values (233 ,12) insert into #test values (543 ,435) insert into #test values (13 ,0) select * from #test Select TOP 1 MyMin From ( Select MIN(Col1) as "MyMin" From #test Where col1 != 0 UNION ALL Select MIN(Col2) as "MyMin" From #test Where col2 != 0) a Order By MyMin drop table #test  |
|||||||||||||||||||||||
Other interesting topics