|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Min functionHi,
This is my scenario .... I have 2 columns .. with values like this. col1 col2 3432 545465 0 6545656 543 54654 54 6456456456 54 45 i want to find out the non-zero minimum out of 2 columns. so i wrote a fn. which takes min(col1),min(col2) and gives me the output. but in this case i dont want to send 0,45 but i want to send non-zero minumum so it would be 54,45. how can i do this ? Thanks RP What do you want to do if a column is all negative values?
Show quote "RP" <R*@discussions.microsoft.com> wrote in message news:6FD1E148-EB4D-4C83-B923-901EA14DD4FF@microsoft.com... > Hi, > This is my scenario .... I have 2 columns .. with values like this. > col1 col2 > 3432 545465 > 0 6545656 > 543 54654 > 54 6456456456 > 54 45 > > i want to find out the non-zero minimum out of 2 columns. > so i wrote a fn. which takes min(col1),min(col2) and gives me the output. > but in this case i dont want to send 0,45 but i want to send non-zero > minumum > so it would be 54,45. how can i do this ? > > Thanks > RP > By the way, i dont have any -ve numbers. it starts from 0
Show quote "Aaron Bertrand [SQL Server MVP]" wrote: > What do you want to do if a column is all negative values? > > > > > "RP" <R*@discussions.microsoft.com> wrote in message > news:6FD1E148-EB4D-4C83-B923-901EA14DD4FF@microsoft.com... > > Hi, > > This is my scenario .... I have 2 columns .. with values like this. > > col1 col2 > > 3432 545465 > > 0 6545656 > > 543 54654 > > 54 6456456456 > > 54 45 > > > > i want to find out the non-zero minimum out of 2 columns. > > so i wrote a fn. which takes min(col1),min(col2) and gives me the output. > > but in this case i dont want to send 0,45 but i want to send non-zero > > minumum > > so it would be 54,45. how can i do this ? > > > > Thanks > > RP > > > > > How about:
select top 1 * from t1 where col1 <> 0 and col2 <> 0 order by col1, col2 Show quote "RP" <R*@discussions.microsoft.com> wrote in message news:6FD1E148-EB4D-4C83-B923-901EA14DD4FF@microsoft.com... > Hi, > This is my scenario .... I have 2 columns .. with values like this. > col1 col2 > 3432 545465 > 0 6545656 > 543 54654 > 54 6456456456 > 54 45 > > i want to find out the non-zero minimum out of 2 columns. > so i wrote a fn. which takes min(col1),min(col2) and gives me the output. > but in this case i dont want to send 0,45 but i want to send non-zero > minumum > so it would be 54,45. how can i do this ? > > Thanks > RP > > select top 1 * This will only work if both min values are in the same row; I think the OP's > from t1 > where col1 <> 0 and col2 <> 0 > order by col1, col2 sample data was convenient but it sounded like he wanted the min in each column individually. If so, maybe we can do it easiest with a UNION: CREATE TABLE blat ( col1 BIGINT, col2 BIGINT ) GO INSERT blat SELECT 3432,545465 INSERT blat SELECT 0 ,6545656 INSERT blat SELECT 543 ,54654 INSERT blat SELECT 54 ,6456456456 INSERT blat SELECT 54 ,450 INSERT blat SELECT 78 ,45 GO SELECT MAX(col1), MAX(col2) FROM ( SELECT col1 = MIN(col1), col2 = 0 FROM blat WHERE col1>0 UNION ALL SELECT col2 = 0, col2 = MIN(col2) FROM blat WHERE col2>0 ) x GO DROP TABLE blat Oh, I thought RP was after the row with the min col1, and within it the min
col2. I might have been wrong. Show quote "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:efwBmJwgFHA.2840@tk2msftngp13.phx.gbl... >> select top 1 * >> from t1 >> where col1 <> 0 and col2 <> 0 >> order by col1, col2 > > This will only work if both min values are in the same row; I think the > OP's sample data was convenient but it sounded like he wanted the min in > each column individually. If so, maybe we can do it easiest with a UNION: > > CREATE TABLE blat > ( > col1 BIGINT, > col2 BIGINT > ) > GO > INSERT blat SELECT 3432,545465 > INSERT blat SELECT 0 ,6545656 > INSERT blat SELECT 543 ,54654 > INSERT blat SELECT 54 ,6456456456 > INSERT blat SELECT 54 ,450 > INSERT blat SELECT 78 ,45 > GO > > SELECT MAX(col1), MAX(col2) > FROM > ( > SELECT col1 = MIN(col1), col2 = 0 FROM blat WHERE col1>0 > UNION ALL > SELECT col2 = 0, col2 = MIN(col2) FROM blat WHERE col2>0 > ) x > GO > > DROP TABLE blat > > Not sure, we could both be wrong. Not enough sample data or clear specs.
Show quote :-( "Itzik Ben-Gan" <it***@REMOVETHIS.SolidQualityLearning.com> wrote in message news:uR$ccWwgFHA.2840@tk2msftngp13.phx.gbl... > Oh, I thought RP was after the row with the min col1, and within it the > min col2. I might have been wrong. AAron,
Thanks for the reply. What i really want to do is - I want to take a non-zero minimum out of both the columns . I didnt know what to do so i was taking min from both the columns and then sending that to a function which will again give me the min of the result. present scenarion anotherfn(min(col1),min(col2)) another fn wiill give me the minimum of both the numbers. Is there anyway i can do that in one shot ??? (Take NONZERO minimum from both the columns ???? Thanks a bunch. RP Show quote "Aaron Bertrand [SQL Server MVP]" wrote: > > select top 1 * > > from t1 > > where col1 <> 0 and col2 <> 0 > > order by col1, col2 > > This will only work if both min values are in the same row; I think the OP's > sample data was convenient but it sounded like he wanted the min in each > column individually. If so, maybe we can do it easiest with a UNION: > > CREATE TABLE blat > ( > col1 BIGINT, > col2 BIGINT > ) > GO > INSERT blat SELECT 3432,545465 > INSERT blat SELECT 0 ,6545656 > INSERT blat SELECT 543 ,54654 > INSERT blat SELECT 54 ,6456456456 > INSERT blat SELECT 54 ,450 > INSERT blat SELECT 78 ,45 > GO > > SELECT MAX(col1), MAX(col2) > FROM > ( > SELECT col1 = MIN(col1), col2 = 0 FROM blat WHERE col1>0 > UNION ALL > SELECT col2 = 0, col2 = MIN(col2) FROM blat WHERE col2>0 > ) x > GO > > DROP TABLE blat > > >  |
|||||||||||||||||||||||
Other interesting topics