GREATEST and LEAST function

  • corallo.d

    SSC-Addicted

    Points: 404

    Hi,

    I'm looking for a function in T-SQL for getting the biggest or the smallest value between two or more columns (like GREATEST function of Oracle or MINDATE/MAXDATE in Firebird)

    Can anyone please advise?

    Thanks in advance,

    D.

  • GSquared

    SSC Guru

    Points: 260824

    I'm not sure if what you're asking for is "max" and "min". Take a look at those in Books Online and see if they will do what you need.

    If you want to return the higher of two values in two different columns, I think a Case statement is needed.

    case

    when Col1 => Col2 then Col1

    when Col2 > Col1 then Col2

    else null

    end

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • corallo.d

    SSC-Addicted

    Points: 404

    Thanks for the fast reply. What I need is not the MAX and MIN. I need a function like COALESCE(field1, field2) which return one of the two fields. The snippet you post it should do the job.

    Kind Regards,

    D.

  • rakesh1084

    SSC Veteran

    Points: 205

    Below two functions are very Similar to least and greatest functions in oracle.

    1. SQL Code for least: -

    GO

    /****** Object: UserDefinedFunction [dbo].[least] Script Date: 08/23/2012 00:53:38 ******/

    /*Author: Rakesh

    Description: This works good for numbers and alphabet.

    */

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE function [dbo].[least] (@str1 nvarchar(max),@str2 nvarchar(max))

    RETURNS nvarchar(max)

    BEGIN

    DECLARE @retVal nvarchar(max);

    set @retVal = (select case when @str1<=@str2 then @str1 end as retVal)

    RETURN @retVal;

    END;

    GO

    Usage: -

    select dbo.least(10,100) LEAST_OF_TWO, dbo.least('R','S') LEAST_OF_TWO_ALPHABET

    2. SQL code for greatest: -

    GO

    /****** Object: UserDefinedFunction [dbo].[greatest] Script Date: 08/23/2012 01:00:56 ******/

    /*Author: Rakesh

    Description: This works good for numbers and alphabet.

    */

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    create function [dbo].[greatest] (@str1 nvarchar(max),@str2 nvarchar(max))

    RETURNS nvarchar(max)

    BEGIN

    DECLARE @retVal nvarchar(max);

    set @retVal = (select case when @str1<=@str2 then @str2 end as retVal)

    RETURN @retVal;

    END;

    GO

    Usage: -

    select dbo.greatest(10,100) GREATEST_OF_TWO, dbo.greatest('a','z') GREATEST_OF_TWO_ALPHABET

  • Michael Valentine Jones

    SSC Guru

    Points: 64818

    GSquared (4/24/2008)


    I'm not sure if what you're asking for is "max" and "min". Take a look at those in Books Online and see if they will do what you need.

    If you want to return the higher of two values in two different columns, I think a Case statement is needed.

    case

    when Col1 => Col2 then Col1

    when Col2 > Col1 then Col2

    else null

    end

    If the columns are nullable, then it gets complex fast.

    Here is an example with 4 columns:

    case

    when a.Val1 is not null and

    (a.Val1 >= a.Val2 or a.Val2 is null) and

    (a.Val1 >= a.Val3 or a.Val3 is null) and

    (a.Val1 >= a.Val4 or a.Val4 is null)

    then a.Val1

    when a.Val2 is not null and

    (a.Val2 >= a.Val1 or a.Val1 is null) and

    (a.Val2 >= a.Val3 or a.Val3 is null) and

    (a.Val2 >= a.Val4 or a.Val4 is null)

    then a.Val2

    when a.Val3 is not null and

    (a.Val3 >= a.Val1 or a.Val1 is null) and

    (a.Val3 >= a.Val2 or a.Val2 is null) and

    (a.Val3 >= a.Val4 or a.Val4 is null)

    then a.Val3

    when a.Val4 is not null and

    (a.Val4 >= a.Val1 or a.Val1 is null) and

    (a.Val4 >= a.Val2 or a.Val2 is null) and

    (a.Val4 >= a.Val3 or a.Val3 is null)

    then a.Val4

    else null

    end

    This is an alternative that is easier to code when you have to do this for a large number of columns:

    Select

    [Max_of_Val1_to_Val4] =

    (

    select

    X1= max(bb.xx)

    from

    (

    select xx = a.Val1 where a.Val1 is not null union all

    select xx = a.Val2 where a.Val2 is not null union all

    select xx = a.Val3 where a.Val3 is not null union all

    select xx = a.Val4 where a.Val4 is not null

    ) bb

    )

    from

    MyTable a

    More about these methods here:

    MIN/MAX Across Multiple Columns

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=86906

    You can also use UNPIVIOT for this. Sorry, don't have an example, but that's what Books Online is for.

    http://msdn.microsoft.com/en-us/library/ms177410(v=sql.90).aspx

  • Paul White

    SSC Guru

    Points: 150442

    Another example, using APPLY syntax:

    DECLARE @T AS TABLE

    (

    pk integer PRIMARY KEY,

    col1 integer NULL,

    col2 integer NULL,

    col3 integer NULL,

    col4 integer NULL

    );

    INSERT @T

    VALUES

    (1, 4, 3, 2, 1),

    (2, 5, NULL, 7, 8);

    SELECT

    MAX(f.x) AS Greatest

    FROM @T AS t

    CROSS APPLY

    (VALUES (col1), (col2), (col3), (col4)) AS f (x)

    GROUP BY t.pk

  • Beer Molleman

    SSC Enthusiast

    Points: 115

    Another solution also using CROSS APPLY

    SELECT MyTable1.x, MyTable2.y,..., G.Greatest

    FROM MyTable1

    JOIN MyTable2 ON MyTable1.key = MyTable2.key

    .

    .

    CROSS APPLY (

    SELECT MAX(T.v) AS Greatest

    FROM ( VALUES (MyTable1.value), (MyTable2.value), ... ) AS T(v)

    ) AS G

    Beer Molleman

  • Dwain Camps

    SSC Guru

    Points: 86893

    Neither of those last two CROSS APPLY examples will work in SS2K5 but this one will:

    DECLARE @T AS TABLE

    (

    pk integer PRIMARY KEY,

    col1 integer NULL,

    col2 integer NULL,

    col3 integer NULL,

    col4 integer NULL

    );

    INSERT @T VALUES (1, 4, 3, 2, 1);

    INSERT @T VALUES (2, 5, NULL, 7, 8);

    SELECT Greatest=MAX(col), Least=MIN(col)

    FROM @T

    CROSS APPLY (

    SELECT col1 UNION ALL SELECT col2 UNION ALL SELECT col3

    UNION ALL SELECT col4) a(col)

    GROUP BY pk

    Admittedly though, I do prefer Paul's for SS2K8!


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • hs_abed

    SSC Enthusiast

    Points: 117

    With Query1 As

    ( Select * from (values (1, 2), (4, 5), (2, Null), (2, 7), (1, 8)) As T(x,y))

    select MAX(x) Greatest, Min(x) Least From Query1

    Union All

    Select Max(y), MIN(y) From Query1

  • John Doe-372015

    SSC Enthusiast

    Points: 119

    rakesh code does'nt work. At all.

    I think it works only with the only one example he tried it...

    (10,100) => works (by chance!!!)

    (100,10) => null, doestn't work (because he didn't do the other case)

    (10, 4) => 10, doesn't work (because the test is wrong anyway.

  • SQLArnold

    SSCommitted

    Points: 1846

    Hello All,

    How would you do this with any and all dates?

    I am trying to think of a way to create a Greatest Value that will handle dates, different date formats, Int, string, any thoughts?

    Thanks In advance!

    The pain of Discipline is far better than the pain of Regret!

  • Jeff Moden

    SSC Guru

    Points: 996829

    SQLArnold (7/24/2015)


    Hello All,

    How would you do this with any and all dates?

    I am trying to think of a way to create a Greatest Value that will handle dates, different date formats, Int, string, any thoughts?

    Thanks In advance!

    The best way would be to store the dates with the correct datatype to begin with. After that, it's the same as the previous examples.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • dmartin 38210

    Valued Member

    Points: 70

    --For those on 2005, and/or wanting a function suited for variable number of columns, below is an alternative.  [I've not tested this widely.]

    CREATE FUNCTION dbo.fn_DelimitedSplitN4K (@pString NVARCHAR(4000), @pDelimiter NCHAR(1)) RETURNS TABLE WITH SCHEMABINDING AS
    RETURN
    /*Used by fn_GreatestCSV.
    Accepts a string & a specified delmiter.  Returns that string as one-row-per-phrase, delimited as specified.
    */

    /*Itzik-Style Cross-Join PseudoCursorCounter Method produces ZERO READS*/
    WITH CTE_E1(n) AS --Alias for 10E1 scientific notation, its just ten SELECT 1's.
     (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
      SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
         ),           -- 1*10^1 [10 rows].
     CTE_E2(n) AS (SELECT 1 FROM CTE_E1 a CROSS JOIN CTE_E1 b), -- 1*10^2 [100 rows[.
     CTE_E4(n) AS (SELECT 1 FROM CTE_E2 a CROSS JOIN CTE_E2 b), -- 1*10^4 [10,000 rows].
     CTE_tally(n) AS
     (SELECT TOP (COALESCE(DATALENGTH(@pString)/2,0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) --We can't order by a constant in ROW_NUMBER(). ROW_NUMBER() won't work without the ORDER BY clause. Use "(SELECT NULL)" to get around both requirements.
     FROM CTE_E4
     ),
     CTE_start AS
     (SELECT 1 AS n1
     UNION ALL
        SELECT t.n + 1 AS n1
     FROM CTE_tally t
     WHERE SUBSTRING(@pString, t.n, 1) = @pDelimiter
     ),
     CTE_len AS
     (SELECT s.n1,
       COALESCE(NULLIF(CHARINDEX(@pDelimiter, @pString, s.n1), 0) - s.N1, 4000) AS l1
        FROM CTE_start s
      )
     SELECT ROW_NUMBER() OVER(ORDER BY l.n1) AS seq,
      SUBSTRING(@pString, l.n1, l.l1) AS phrase
     FROM CTE_len l;

    CREATE FUNCTION fn_GreatestCSV (@pString NVARCHAR(4000), @isOrdReturn bit) RETURNS dec(18,6)
    /*
    When @isOrdReturn equals False this function returns greatest numbers among supplied values in CSV; otherwise returns column position of that greatest value.
    */
    BEGIN
     DECLARE @mx AS dec(18,6) ;
     DECLARE @ord AS dec(18,6)

        SET @mx = (SELECT MAX(CAST(ds.phrase AS dec(18,6)))
        FROM dbo.fn_DelimitedSplitN4K(@pString, ',') ds
        )
     SET @Ord = (SELECT ds.seq
        FROM dbo.fn_DelimitedSplitN4K(@pString, ',') ds
        WHERE CAST(ds.phrase AS dec(18,6)) = @mx
        )

     IF @isOrdReturn = 1 SET @mx = @Ord
        RETURN @mx ;
    END

    --Example function calls: 

    SELECT dbo.fn_GreatestCSV('1,8.03,3,4,5', 0)      --Returns value of 8.030000.
    SELECT dbo.fn_GreatestCSV('1,8.03,3,4,5', 1)      --Returns column # 2.

  • SQLArnold

    SSCommitted

    Points: 1846

    dmartin 38210 , I am a huge fan of Itzik. This is a great option, thanks!
    I agree Jeff however, we do not have control how data sent to us is formatted, this is the problem. Each source, the State, has there way(ideas) of doing things, which are not always best practices.
    We get date formats like
    DDMMYYYY, YYYYMMDD, YYYMMDD, CCMMDD, DDMMYY

    The pain of Discipline is far better than the pain of Regret!

  • ChrisM@Work

    SSC Guru

    Points: 186107

    dmartin 38210 - Wednesday, May 31, 2017 5:59 PM

    --For those on 2005, and/or wanting a function suited for variable number of columns, below is an alternative.  [I've not tested this widely.]

    CREATE FUNCTION dbo.fn_DelimitedSplitN4K (@pString NVARCHAR(4000), @pDelimiter NCHAR(1)) RETURNS TABLE WITH SCHEMABINDING AS
    RETURN
    /*Used by fn_GreatestCSV.
    Accepts a string & a specified delmiter.  Returns that string as one-row-per-phrase, delimited as specified.
    */

    /*Itzik-Style Cross-Join PseudoCursorCounter Method produces ZERO READS*/
    WITH CTE_E1(n) AS --Alias for 10E1 scientific notation, its just ten SELECT 1's.
     (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
      SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
         ),           -- 1*10^1 [10 rows].
     CTE_E2(n) AS (SELECT 1 FROM CTE_E1 a CROSS JOIN CTE_E1 b), -- 1*10^2 [100 rows[.
     CTE_E4(n) AS (SELECT 1 FROM CTE_E2 a CROSS JOIN CTE_E2 b), -- 1*10^4 [10,000 rows].
     CTE_tally(n) AS
     (SELECT TOP (COALESCE(DATALENGTH(@pString)/2,0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) --We can't order by a constant in ROW_NUMBER(). ROW_NUMBER() won't work without the ORDER BY clause. Use "(SELECT NULL)" to get around both requirements.
     FROM CTE_E4
     ),
     CTE_start AS
     (SELECT 1 AS n1
     UNION ALL
        SELECT t.n + 1 AS n1
     FROM CTE_tally t
     WHERE SUBSTRING(@pString, t.n, 1) = @pDelimiter
     ),
     CTE_len AS
     (SELECT s.n1,
       COALESCE(NULLIF(CHARINDEX(@pDelimiter, @pString, s.n1), 0) - s.N1, 4000) AS l1
        FROM CTE_start s
      )
     SELECT ROW_NUMBER() OVER(ORDER BY l.n1) AS seq,
      SUBSTRING(@pString, l.n1, l.l1) AS phrase
     FROM CTE_len l;

    CREATE FUNCTION fn_GreatestCSV (@pString NVARCHAR(4000), @isOrdReturn bit) RETURNS dec(18,6)
    /*
    When @isOrdReturn equals False this function returns greatest numbers among supplied values in CSV; otherwise returns column position of that greatest value.
    */
    BEGIN
     DECLARE @mx AS dec(18,6) ;
     DECLARE @ord AS dec(18,6)

        SET @mx = (SELECT MAX(CAST(ds.phrase AS dec(18,6)))
        FROM dbo.fn_DelimitedSplitN4K(@pString, ',') ds
        )
     SET @Ord = (SELECT ds.seq
        FROM dbo.fn_DelimitedSplitN4K(@pString, ',') ds
        WHERE CAST(ds.phrase AS dec(18,6)) = @mx
        )

     IF @isOrdReturn = 1 SET @mx = @Ord
        RETURN @mx ;
    END

    --Example function calls: 

    SELECT dbo.fn_GreatestCSV('1,8.03,3,4,5', 0)      --Returns value of 8.030000.
    SELECT dbo.fn_GreatestCSV('1,8.03,3,4,5', 1)      --Returns column # 2.

    --probably more efficient to put something

    -- likethis into an APPLY block:

    SELECT TOP(1) seq, phrase

    FROM dbo.fn_DelimitedSplitN4K(@pString, ',')

    ORDER BY phrase DESC

    [/code]
    - casting as required for the sort.

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply