Get the minimum value between several columns

Question

I'm using SQL Server 2008;

Suppose I have a table 'X' with columns 'Date1', 'Date2', 'Dateblah', all of type DateTime.

I want to select the min value between the three columns, for example (simplified, with date mm/dd/yyyy)

ID       Date1          Date2           Dateblah
0     09/29/2011      09/20/2011       09/01/2011 
1     01/01/2011      01/05/2011       03/03/2010

ID    MinDate
0    09/01/2011
1    03/03/2010

Is there a bread and butter command to do that ?

Thanks in advance.

EDIT: I've seen this question What's the best way to select the minimum value from several columns? but unfortunately it won't suit me as I'm being obligated to do it against normalization because I'm making tfs work item reports, and the 'brute-force' case thing will end up being a pain if I have 6 ou 7 columns.

You (or anyone else reading this) might want to check a [new answer](https://stackoverflow.com/a/55747020/3336667) at that question. Yes that answer is mine, but does not use repeatedly-mentioned answer of large switch, new function or changing tables, adding triggers etc., it uses temp table to transpose structure and then common `min` function. I am posting is comment as this is not answer by itself. — Rao, Apr 18 '19 at 13:45

score 9 · Answer 1 · edited May 23 '17 at 12:23

9

based on scalar function (from Tom Hunter):

SELECT ID, (SELECT MIN([date]) FROM (VALUES(Date1),(Date2),(Dateblah)) x([date])) MinDate
FROM TableName

edited May 23 '17 at 12:23

Community

1
1

answered Feb 05 '13 at 16:20

Israel

91
1
1

score 6 · Accepted Answer · answered Sep 29 '11 at 13:46

6

There is no built in function to return the min/max of two (or more) columns. You could implement your own scalar function to do this.

In SQL Server 2005+ you could use UNPIVOT to turn the columns into rows and then use the MIN function:

CREATE TABLE [X]
(
    [ID] INT,
    [Date1] DATETIME,
    [Date2] DATETIME,
    [Date3] DATETIME
)

INSERT  [X]
VALUES  (0, '09/29/2011', '09/20/2011', '09/01/2011'),
        (1, '01/01/2011', '01/05/2011', '03/03/2010')


SELECT [ID], MIN([Date]) AS [MinDate]
FROM [X]
UNPIVOT (
    [Date] FOR d IN
        ([Date1]
        ,[Date2]
        ,[Date3])
) unpvt
GROUP BY [ID]

answered Sep 29 '11 at 13:46

Tom Hunter

5,714
10
51
76

Thank you, it works perfectly, I'll mark as answer as soon as I can – Conrad Clark Sep 29 '11 at 13:49
I just tried to run this code on my server (SQL SErver 2008 R2) and received an error related to the last statement. "Incorrect syntax near 'Date'." – Everette Mills Feb 08 '13 at 22:13
I'm not sure why you're getting this. Are you running the script through SQL Server Management Studio? I just tried it again against a 2008 R2 instance and it works fine for me.. – Tom Hunter Feb 09 '13 at 20:17
I think I figured out the issue. I am not the db admin and it seems we have some sort of compatibly check in place that ensures some older code from 10 + years ago works correctly on the server I was working with. I think this is preventing the unpivot command from working correctly. When I tired on a new server it worked correctly. – Everette Mills Feb 11 '13 at 15:35
Yep, looks like your database compatibility level is set to 80 (SQL Server 2000) - I can re-create the error if I change mine to 80 (ALTER DATABASE [Tom] SET COMPATIBILITY_LEVEL = 80). The code works with level 90 or 100 set. – Tom Hunter Feb 11 '13 at 21:45

score 1 · Answer 3 · answered Sep 29 '11 at 14:08

Implementing a scalar function:

CREATE FUNCTION [dbo].[MIN](@a SQL_VARIANT, @b SQL_VARIANT)
RETURNS SQL_VARIANT
AS 
BEGIN
    RETURN (
        SELECT MIN([x])
        FROM (VALUES(@a),(@b)) x([x])
    )   
END
GO

DECLARE @a DATETIME = '12 JUL 2011', @b DATETIME = '20 AUG 2011'
SELECT [dbo].[MIN](@a, @b)

DECLARE @c INT = 12, @d INT = 32
SELECT [dbo].[MIN](@c, @d)

score 0 · Answer 4 · edited Apr 23 '15 at 21:52

0

Simply lets say the table where your dates are is called sells and it has two date fields Date1 and Date2 from which you want the minimum.

SELECT ( 
   SELECT MIN([x]) 
   FROM (VALUES(Date1),(Date2)) x([x])
) as minimum
FROM sells

edited Apr 23 '15 at 21:52

Nizam

4,569
3
43
60

answered Jul 26 '14 at 15:19

Javier Lisandro

1

Get the minimum value between several columns

4 Answers4

Linked