Min Value Function

I have a function which returns the minimum value

CREATE FUNCTION Getname2
               (@Int1     FLOAT(8), 
                @package1 VARCHAR(50), 
                @Int2     FLOAT(8), 
                @package2 VARCHAR(50), 
                @Int3     FLOAT(8), 
                @package3 VARCHAR(50) 
                )
RETURNS VARCHAR(50)
  BEGIN 
    DECLARE  @AllValues  TABLE( 
                               INPUT   FLOAT(8), 
                               package VARCHAR(50) 
                               ) 
     
    INSERT INTO @AllValues 
    VALUES     (@Int1, @package1)  
     
    INSERT INTO @AllValues 
    VALUES     (@Int2, @package2)  
     
    INSERT INTO @AllValues 
    VALUES     (@Int3, @package3)  
     
    RETURN 
      (SELECT package
       FROM   @AllValues 
       WHERE  [Input] != 0 and input = (select min(input) from @AllValues ))  
  END 
  
  
If I call the function with variables it gives the perfect result

  select Calls.dbo.Getname2(1,'Morning',2,'Evening',3,'Night')

but if I use where there are two minimum values it gives error “Subquery 
returned more than 1 value”,
  
select Calls.dbo.Getname2(1,'Morning',1,'Evening',3,'Night')
Is this possible that the if there are two minimum values it return randomly 
any single value i.e,

Morning or Evening

Secondly how to return both values of int and packag on a single call.


Regards,
Muhammad Bilal
  

0
Utf
3/31/2010 1:08:10 PM
sqlserver.server 1327 articles. 0 followers. Follow

1 Replies
658 Views

Similar Articles

[PageSpeed] 30

You can change the last query in the function to return always a scalar value:

SELECT TOP 1 package
FROM   @AllValues
WHERE  input <> 0
ORDER BY input;

To return multiple columns you have to transform the function to table-valued function:

CREATE FUNCTION Getname2
                (@Int1     FLOAT(8),
                 @package1 VARCHAR(50),
                 @Int2     FLOAT(8),
                 @package2 VARCHAR(50),
                 @Int3     FLOAT(8),
                 @package3 VARCHAR(50)
                 )
RETURNS TABLE
     RETURN (SELECT TOP 1 input, package
             FROM (SELECT @Int1, @package1
                   UNION ALL
                   SELECT @Int2, @package2
                   UNION ALL
                   SELECT @Int3, @package3) AS T(input, package)
             ORDER BY input);

And then call it:

SELECT input, package
FROM Getname2(1, 'Morning', 1, 'Evening', 3, 'Night');

-- 
Plamen Ratchev
http://www.SQLStudio.com
0
Plamen
3/31/2010 1:35:41 PM
Reply:

Similar Artilces:

#value error
Hi, In the cell B9 the value is 01/09/2006 In the cell C9 i enter the formula =IF(ISBLANK(B9),"",DATE(YEAR(B9),MONTH(B9)+1,DAY(B9))) In the cell D9 i enter the same formula =IF(ISBLANK(C9),"",DATE(YEAR(C9),MONTH(C9)+1,DAY(C9))) It works fine. But if i delete the value in B9, cell c9 displays blank. But cell D9 displays #value error. Is there any way to supress this error and display balnk value in D9. Any help appreciated. Suresh -- mtpsuresh ------------------------------------------------------------------------ mtpsuresh's Profile: http://www.excelforum.co...

How to choose maximum value
Hi, I am wondering if anybody can help me with this. I need to take some information from Table 1 to a new table which I am working on. For example, for "apple" in column A, I would like to find the highest number (30) in Column B and return the correspnonding text in Column C (a2) to the new table. Table 1 columnA ColumnB ColumnC apple 2 a1 apple 30 a2 apple 15 a3 apple 6 a4 apple 12 a5 apple 9 a6 banana 3 b1 banana 10 b2 orange 2 o1 orange 1 o2 cherry 10 c1 peanut 20 p1 peanut 50 p2 peanut 25 p3 tomato 2 t1 tomato l t1 t...

display value in combo box
I populate a combox1.rowsource = value1. How do I display value1 in the combo box? I can see it in the dropdown, but how do I display it? Thanks. Kou Do you have it bound to a field in your table? -- KARL DEWEY Build a little - Test a little "Kou Vang" wrote: > I populate a combox1.rowsource = value1. How do I display value1 in the > combo box? I can see it in the dropdown, but how do I display it? Thanks. > > Kou On Thu, 13 Dec 2007 09:19:02 -0800, Kou Vang wrote: > I populate a combox1.rowsource = value1. How do I display value1 in the > combo b...

Setvalue property to default value
I'm Trying to run macro for set property to set the object to default value related to another object in sub-subform, but I getting the following message, “ you trying to run procedure doesn’t contain the automation object. etc Yousoft, Can you please give the exact details of the macro. I.e. the Actions, and relevant Arguments. Thanks, that will help us to understand. -- Steve Schapel, Microsoft Access MVP Yousoft wrote: > I'm Trying to run macro for set property to set the object to default value > related to another object in sub-subform, but I getting the followi...

need help sorting text by trailing Numbers Value
I am looking for some help sorting a text field (PartNumbers) alphabetically but correctly depending on the value of the ending few charachters if theyre numbers.. Here is some sample data an960pd10 an960pd300 an960pd6 i would like it sorted like this an960pd6 an960pd10 an960pd300 any ideas? Thanks Barry I looks like your values have a fixed length to the left of the numbers. If this is true, you can use the following in the Sorting and Grouping dialog expression: =Left([PartNumbers],7) =Val(Mid([PartNumbers],8)) -- Duane Hookom Microsoft Access MVP ...

MIN formula help
Hello All, I want to know what the Max and Min numbers are using the following formulas. The Max formula seems to work without any problem, however it's the Min that I have a problem with. =Max(A2:A10) =MIN(A2:A10) If I only have numbers in cells A2:A8 and the lowest number is 150, then in that cell that I have the following MIN formula in should show me 150. Instead it's blank because I still have two remaining cells in my range (A9 and A10). How can I fix this. Any and all help is greatly appreciated.. Hi! MIN/MAX ignore empty cells. >Instead it's blank because I ...

copy range of unique value
Hi, i use Excel 2007 and i have a strange behavior with a particular thing that i do. quite often i have list of values with several times the same values. e.g.: 2,3,8,5,4,3,9 from cell A1 to A7 when i use the function: Data => Sort & Filter => Advanced => Copy to another location, list range $A$1:$A$7, copy to $D$1 and checked Unique records only. i correctly get 2,3,8,5,4,9 it means second times that 3 is found, it is removed from the new list. i have also a good result with only alpha value and only numerical values. my main problem is when cells have ...

Adding default value to pre-existing table
I am working within a database that I created using a downloaded text file. I have to add a column to the table that will contain the same value in each field. Is there a way to add this default value to the preexisting table without keyboarding it in? Thanks in advance for any help. Create a select query on that table and field. Run it and see what it return. Next change this query into an Update query. Put in the value that you want into Update To. If you don't want to overwrite any existing data, just update empty records, put Is Null in the criteria. -- Jerry Whit...

Problem writing Time-Out function
Alright this one might seem a little odd. Basically when I initialize my dialog I have to call a routine (RunSameThread) which unfortunately calls some functions set with a library (which I don't have the source code for) which may not exit. (Basically this call should initialize the COM port and send's some commands to a transceiver attached to the port, then wait for a response before returning). Now, if the transceiver is not hooked up (or turned on) the COM port initialization will go though, but the library function will sit in an infinite loop :( Thus, I am trying to write a t...

Make a text box expand or shrink based on size of the value
I am working on a report in design view. Is there a way to have a text box get longer or shrink according to the value? For example, one of the headers is "Operations" while another header is "Resource Integration." ***Since the text box has a blue fill, there will be empty space within the text box if I make it large enough to fit the longest word. Therefore, I want the text box to expand or shrink as necessary. Any input is appreciated. Thanks! Glen Set the 'Can Grow' property to Yes. -- Build a little, test a little. "*G...

needs double value in string format
I have a double variable Dim d as double. d gets values stored as 2010031266671939.0 I need this value in string. If I use d.tostring(), it provides in E+ format. I need this double value in string without E+ format. If I use d.Tostring("#.0"), it rounds off the double value & provides as 2010031266671940.0. I need the value as such. Can anyone help me -- Thanks & regards, V.Vallikkannu Project Leader Chella Software Private Limited | Mobile : <9944254599.> | Off: +91 452 4262000 | www.chelsoft.com This e-mail and any files transmitted wit...

test for value and return position
I am trying to accomplish two things. 1) I want to test for the existance of a value in a range of number that varies from day to day. 2) I want to return the cell reference of the data obtained in question (1) Any IDeas? I though of using something akin to H2:INDEX(H:H,COUNT(H:H)+1)) but I think I'm not on target here Jeff On Fri, 10 Dec 2004 02:54:28 GMT, "Buster" <bubs@gmail.com> wrote: >I am trying to accomplish two things. >1) I want to test for the existance of a value in a range of number that >varies from day to day. > >2) I want to return th...

Backcolor value in hex
I want to get the hex value of a cell's backcolor, so I can replicate the same color in Access. In VBA, I try to derive this by using Hex(sheet.range(cell).interior.color). I get a hex value, but when I try it in Access, it displays a different color. Am I grabbing the correct property from the cell in Excel? Or, is there another way I can derive the hex value? Thanks in advance. What version of Excel/Access are you using 2003 or 2007? I'm believe you can set the color using HSL or RGB scales, which apply to both Excel and Access (at least in 2003). -- If this helps, pl...

vLookup, Look up Value
Hi, I have problems when the look up value in the vLookup formula is a date, and the cell has a differente format. For example, I specify 03/03/10 as the look up value, but the cell has short date format, so the real value in the cell is 03/03/2010. As a result, the formula displays #N/A. How can I solve this? Thanks in advance Regards, Emece.- It would help to see your VLOOKUP() formula. But I suspect it may look something like: =VLOOKUP("03/02/10",Sheet2!A1:B99,2,FALSE) and in Sheet2, column A you actually have dates. So the text you've entered ( ...

Return values from a list that sum to a known value
Is it possible to return values from a list that sum to a known product, for example: 10 1 2 7 6 9 The returned list would be 9,1 and 7,2,1. Thanks. ...

Hide rows with zero value?
just wondering how I would be able to hide rows on sheets if some cells are blank? the cells contain formulas they are only blank because `zero values` in >tools>options>view is unchecked. I have a sheet set up to SUMIF values from about 16 other sheets, and it returns the values depending on 72 corresponding codes, I would like to hide any of the 72 rows of data that are blank. the values returned will change on a week to week and daily basis, so I would like to be able to use the same design for each new week, hope that makes sense. Hi try something like the following: Sub hide...

Charts and values
Hi All I have an excel column chart with a range of values. Some of the values are so low compared with the main figures (eg. 92% against 0.07%). Are there any suggestions to make the chart more presentable. I have tried adding the value and series name to the column but it looks a bit messy. Ideally I would like a little sub chart showing the lesser values. Is this possible? Any suggestions, ideas or comments appreciated Cheers Rexmann Try http://www.contextures.com/charts.html. This site contains links to other sites with chart tips and techniques including stacking charts. &quo...

determine which cell a value is returned from
How to determine which cell a value is returned from. e.g.=MAX(D6:CC280) returns 525 - How do I find the cell where it occurs? Hi one way =CELL("address",INDEX(D6:CC280,MATCH(MAX(D6:CC280),D6:CC280,0))) Cheers JulieD "curiousg" <curiousg@discussions.microsoft.com> wrote in message news:07BEB420-D231-4A8A-A320-981F9F09F7C1@microsoft.com... > How to determine which cell a value is returned from. e.g.=MAX(D6:CC280) > returns 525 - How do I find the cell where it occurs? One way: =ADDRESS(MAX(IF(MAX(D6:CC280)=D6:CC280,ROW(D6:CC280))),MAX (IF(MAX(D6:CC28...

Min bal requirement
My checking account has a minimum balance requirement of $250 to waive the service charge fees, so I want to keep the minimum in the account. Any recommendations on how to balance to the statement? Should I deduct the $250 from the check register & try to remember this every month when I balance to subtract the $250 from the bank's balance? Is there an easier way? Thanks. =?Utf-8?B?S2VsaUI=?= <KeliB@discussions.microsoft.com> wrote on 08 Sep 2007 in group microsoft.public.money: > My checking account has a minimum balance requirement of $250 to > waive the servic...

Lookup based on matrix values
I am struggling to develop one of those "copy down" functions that can produce a set of values. In this simplified example, the formula needs to tell me the name from column A if there is a value in the remaining array (B1:D5). Dave 1 2 3 Bob 1 Sarah 3 George 2 Sally 1 So the first instance of the formula would produce "Dave", the second would produce "Bob", the third ""George", the fourth is "Dave" again, followed by "Sarah", etc. Yes, I need i...

seach function in Outlook XP
Hi, Used software: Windows XP (Dutch) or w2k (Dutch and English), Outlook XP(Dutch), on an Exchangeserver (2002 ?). When using the search function in Outlook XP there is no result, whether searching on subject, sender or mail text. There is no result, all mail disappears. Only by leaving the search field blank all mail shows up again. The problem appears both with w2k and xp operating systems (English and Dutch), but only using office xp (Dutch). Outlook98 worked OK on the same server! By using webmail the problem does not exist. (The improved search options should be on of the main advant...

how do i convert Min:Sec to Min.sec for example 68:43:00 = 68.43
how do i convert Min:Sec to Min.sec for example 68:43:00 = 68.43 (68 minutes and 43 seconds) In article <5F13B001-9B6B-4D5D-BD81-ACAAF5799CF6@microsoft.com>, "=? Utf-8?B?dmVsZSBQaGFudA==?=" <vele Phant@discussions.microsoft.com> says... > how do i convert Min:Sec to Min.sec for example 68:43:00 = 68.43 (68 minutes > and 43 seconds) > =MINUTE(C31)+SECOND(C31)/100 where C31 contains the time in hh:mm:ss. Note that XL may format the cell containing the formula with a time format. If so, change it to 'General'. -- Regards, Tushar Mehta www.tushar-m...

IF Function #19
Hi, I want to use an IF function to examine the contents of a cell and if the cells contains a numeric value then do something else do something else. How do I check if something is numeric? Thanks Simon -- sgrech ------------------------------------------------------------------------ sgrech's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=14501 View this thread: http://www.excelforum.com/showthread.php?threadid=466914 Use the ISNUMBER() function within your IF() function. Like this: =IF(ISNUMBER(A1),A1,"") This will result in the value of A1...

Ordering the chart by value
Hi I have a stacked column graph which shows me how many people have been sick over the month and for what reason. The months are on the horizontal axis with the reason creating the stacked columns. Is there anyway of ordering the stacked columns by values ie the greatest used reason code is always at the bottom and the least used at the top. I am currently struggling with it as each month the order will change and wasnt sure if the graph can be ordered like that. -- Thanks Ruth ...

Scatter Charts
I am using an XY scatter chart with data that looks like Name Priority Date Lic/Ow Free 12/16/1998 Lic Warner 1/28/1999 Lic Buckmon 7/12/2001 Ow Tucker 12/28/2000 Ow The Y values are the priority dates and x values would Lic or Own. Each dataset would be referenced in vertical columns. The problem is how to change data labels so that reflect the data in the name column and the data in the Lic/Own column. Hope you can help. Thanks. Thanks but I was able to use the XY Chart Labeler from Ap...