Results 1 to 13 of 13
  1. #1
    Join Date
    Mar 2004
    Location
    Maryland
    Posts
    152

    Unanswered: Least Function in SQL Server

    Hi,

    I want to use least function equalant in SQL Server. my statement is like this:
    SELECT SUM(PRICE_WORDS)/(least(9,5)-1) from ABC

    Which fucntion I would use?

    Any help will be highly appreciated.
    mr_roomi

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    you have to tell us what LEAST does.....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd probably build a case statement for this example.

    -PatP

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Brett Kaiser
    you have to tell us what LEAST does.....
    Least() is an Oracle PL/SQL function that returns the smallest of a list of items. A CASE statement would be my choice here.

    -PatP

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK...since it's probably dynamic...

    I'd load them all to a table variable then SELECT @Col1=MIN(Col1) FROM @t

    How are you getting the values in the first place?

    EDIT: Never had to use LEAST and all my Oracle books are at home....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Mar 2004
    Location
    Maryland
    Posts
    152

    Least Means

    hi,

    Least returns the smallest of the values in the list.This function is used for multiple values in the same row.(but it is not equalant to MIN.

    Syntax
    LEAST(expr [,expr] ...)

    Purpose
    Returns the least of the list of exprs. All exprs after the first are implicitly converted to the datatype of the first expr before the comparison. Oracle compares the exprs using nonpadded comparison semantics. If the value returned by this function is character data, its datatype is always VARCHAR2.


    Example
    SELECT LEAST('HARRY','HARRIOT','HAROLD') "LEAST"
    FROM DUAL;

    LEAST
    ------
    HAROLD
    mr_roomi

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well if it's no the same as MIN...what's the difference?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Mar 2004
    Location
    Maryland
    Posts
    152

    Cool solution

    Hi Brett,

    Solution is Case statement.

    Thanks for prompt reply.
    mr_roomi

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    For your original example, I'd use:
    Code:
    SELECT Sum(PRICE_WORDS) / CASE WHEN 9 < 5 THEN 9 ELSE 5 END - 1)
       FROM ABC
    For your second example, I'd use:
    Code:
    SELECT CASE
       WHEN 'HARRY' <= 'HARRIOT' AND 'HARRY' <= 'HAROLD' THEN 'HARRY'
       WHEN 'HARRIOT' <= 'HARRY' AND 'HARRIOT' <= 'HAROLD' THEN 'HARRIOT'
       ELSE 'HAROLD' END AS LEAST
    -PatP

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Someone please tell me how you are going to use CASE When the values are dynamic?

    What if there are n values?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If the values themselves are dynamic, no problem. You can use variables, column names, and constants mix-n-match to your heart's content. If the number of values are dynamic, you'll have to resort to some form of black magic, but I haven't seen anything that makes me think that might be the case.

    -PatP

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    you mean the new sql extension...T-voodoo?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Yeah, something like that. Too bad you can't do functions using XP (extended stored procedure) like extensions... Now that would really rock!

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •