SQL MAX wielu kolumn?

Jak zwrócić 1 wartość na wiersz z maksymalnie kilku kolumn:

Nazwa tabeli

[Number, Date1, Date2, Date3, Cost]

Muszę zwrócić coś takiego:

[Number, Most_Recent_Date, Cost]

Pytanie?

Możesz użyć instrukcji CASE:

SELECT
    CASE
        WHEN Date1 >= Date2 AND Date1 >= Date3 THEN Date1
        WHEN Date2 >= Date1 AND Date2 >= Date3 THEN Date2
        WHEN Date3 >= Date1 AND Date3 >= Date2 THEN Date3
        ELSE                                        Date1
    END AS MostRecentDate

[W przypadku Microsoft SQL Server 2008 i nowszych, możesz rozważyć prostszą odpowiedź Svena poniżej.]

Oto kolejne fajne rozwiązanie dla Maxfunkcjonalności wykorzystującej T-SQL i SQL Server

SELECT [Other Fields],
  (SELECT Max(v) 
   FROM (VALUES (date1), (date2), (date3),...) AS value(v)) as [MaxDate]
FROM [YourTableName]

Jeśli używasz MySQL, możesz użyć

SELECT GREATEST(col1, col2 ...) FROM table

Istnieją jeszcze 3 metody, w których UNPIVOT(1) jest zdecydowanie najszybszy, a następnie Symulowane odwracanie (3), które jest znacznie wolniejsze niż (1), ale wciąż szybsze niż (2)

CREATE TABLE dates
    (
      number INT PRIMARY KEY ,
      date1 DATETIME ,
      date2 DATETIME ,
      date3 DATETIME ,
      cost INT
    )

INSERT  INTO dates
VALUES  ( 1, '1/1/2008', '2/4/2008', '3/1/2008', 10 )
INSERT  INTO dates
VALUES  ( 2, '1/2/2008', '2/3/2008', '3/3/2008', 20 )
INSERT  INTO dates
VALUES  ( 3, '1/3/2008', '2/2/2008', '3/2/2008', 30 )
INSERT  INTO dates
VALUES  ( 4, '1/4/2008', '2/1/2008', '3/4/2008', 40 )
GO

Rozwiązanie 1 ( UNPIVOT)

SELECT  number ,
        MAX(dDate) maxDate ,
        cost
FROM    dates UNPIVOT ( dDate FOR nDate IN ( Date1, Date2,
                                            Date3 ) ) as u
GROUP BY number ,
        cost 
GO

Rozwiązanie 2 (zapytanie podrzędne na wiersz)

SELECT  number ,
        ( SELECT    MAX(dDate) maxDate
          FROM      ( SELECT    d.date1 AS dDate
                      UNION
                      SELECT    d.date2
                      UNION
                      SELECT    d.date3
                    ) a
        ) MaxDate ,
        Cost
FROM    dates d
GO

Rozwiązanie 3 (symulowane UNPIVOT)

;WITH    maxD
          AS ( SELECT   number ,
                        MAX(CASE rn
                              WHEN 1 THEN Date1
                              WHEN 2 THEN date2
                              ELSE date3
                            END) AS maxDate
               FROM     dates a
                        CROSS JOIN ( SELECT 1 AS rn
                                     UNION
                                     SELECT 2
                                     UNION
                                     SELECT 3
                                   ) b
               GROUP BY Number
             )
    SELECT  dates.number ,
            maxD.maxDate ,
            dates.cost
    FROM    dates
            INNER JOIN MaxD ON dates.number = maxD.number
GO

DROP TABLE dates
GO

Każda z dwóch poniższych próbek będzie działać:

SELECT  MAX(date_columns) AS max_date
FROM    ( (SELECT   date1 AS date_columns
           FROM     data_table         )
          UNION
          ( SELECT  date2 AS date_columns
            FROM    data_table
          )
          UNION
          ( SELECT  date3 AS date_columns
            FROM    data_table
          )
        ) AS date_query

Drugi to dodatek do odpowiedzi lassevka .

SELECT  MAX(MostRecentDate)
FROM    ( SELECT    CASE WHEN date1 >= date2
                              AND date1 >= date3 THEN date1
                         WHEN date2 >= date1
                              AND date2 >= date3 THEN date2
                         WHEN date3 >= date1
                              AND date3 >= date2 THEN date3
                         ELSE date1
                    END AS MostRecentDate
          FROM      data_table
        ) AS date_query 

Dla T-SQL (MSSQL 2008+)

SELECT
  (SELECT
     MAX(MyMaxName) 
   FROM ( VALUES 
            (MAX(Field1)), 
            (MAX(Field2)) 
        ) MyAlias(MyMaxName)
  ) 
FROM MyTable1

DECLARE @TableName TABLE (Number INT, Date1 DATETIME, Date2 DATETIME, Date3 DATETIME, Cost MONEY)

INSERT INTO @TableName 
SELECT 1, '20000101', '20010101','20020101',100 UNION ALL
SELECT 2, '20000101', '19900101','19980101',99 

SELECT Number,
       Cost  ,
       (SELECT MAX([Date])
       FROM    (SELECT Date1 AS [Date]
               UNION ALL
               SELECT Date2
               UNION ALL
               SELECT Date3
               )
               D
       )
       [Most Recent Date]
FROM   @TableName

Funkcja skalarna powoduje różnego rodzaju problemy z wydajnością, więc lepiej jest zawijać logikę w funkcję wycenioną w tabeli wbudowanej, jeśli to możliwe. Jest to funkcja, której użyłem do zastąpienia niektórych funkcji zdefiniowanych przez użytkownika, które wybrały daty Min / Max z listy maksymalnie dziesięciu dat. Podczas testowania na moim zestawie danych z 1 milionem wierszy funkcja skalarna zajęła ponad 15 minut, zanim zabiłem zapytanie, Inline TVF zajęło 1 minutę, co jest tym samym czasem, co wybranie zestawu wyników do tabeli tymczasowej. Aby użyć tego wywołania, należy użyć funkcji z podzapytania w SELECT lub CROSS APPLY.

CREATE FUNCTION dbo.Get_Min_Max_Date
(
    @Date1  datetime,
    @Date2  datetime,
    @Date3  datetime,
    @Date4  datetime,
    @Date5  datetime,
    @Date6  datetime,
    @Date7  datetime,
    @Date8  datetime,
    @Date9  datetime,
    @Date10 datetime
)
RETURNS TABLE
AS
RETURN
(
    SELECT      Max(DateValue)  Max_Date,
                Min(DateValue)  Min_Date
    FROM        (
                    VALUES  (@Date1),
                            (@Date2),
                            (@Date3),
                            (@Date4),
                            (@Date5),
                            (@Date6),
                            (@Date7),
                            (@Date8),
                            (@Date9),
                            (@Date10)
                )   AS Dates(DateValue)
)

SELECT 
    CASE 
        WHEN Date1 >= Date2 AND Date1 >= Date3 THEN Date1 
        WHEN Date2 >= Date3 THEN Date2 
        ELSE Date3
    END AS MostRecentDate 

Jest to nieco łatwiejsze do napisania i pomija etapy oceny, ponieważ instrukcja przypadku jest oceniana w kolejności.

Niestety odpowiedź Lasse , choć z pozoru oczywista, ma poważną wadę. Nie obsługuje wartości NULL. Każda pojedyncza wartość NULL powoduje zwrócenie daty 1. Niestety każda próba rozwiązania tego problemu staje się bardzo nieuporządkowana i nie skaluje się do 4 lub więcej wartości bardzo ładnie.

Pierwsza odpowiedź na databyss wyglądała (i jest) dobra. Nie było jednak jasne, czy odpowiedź łatwo ekstrapoluje do 3 wartości z łączenia wielu tabel zamiast prostszych 3 wartości z jednej tabeli. Chciałem uniknąć przekształcenia takiego zapytania w podwykonanie, aby uzyskać maksymalnie 3 kolumny, byłem też pewien, że doskonały pomysł bazy danych można trochę wyczyścić.

Więc bez zbędnych ceregieli, oto moje rozwiązanie (zaczerpnięte z pomysłu bazy danych).
Używa sprzężeń krzyżowych, wybierając stałe, aby symulować efekt łączenia wielu tabel. Ważną rzeczą do zapamiętania jest to, że wszystkie niezbędne aliasy przenoszą się poprawnie (co nie zawsze ma miejsce), a to sprawia, że ​​wzór jest dość prosty i dość skalowalny poprzez dodatkowe kolumny.

DECLARE @v1 INT ,
        @v2 INT ,
        @v3 INT
--SET @v1 = 1 --Comment out SET statements to experiment with 
              --various combinations of NULL values
SET @v2 = 2
SET @v3 = 3

SELECT  ( SELECT    MAX(Vals)
          FROM      ( SELECT    v1 AS Vals
                      UNION
                      SELECT    v2
                      UNION
                      SELECT    v3
                    ) tmp
          WHERE     Vals IS NOT NULL -- This eliminates NULL warning

        ) AS MaxVal
FROM    ( SELECT    @v1 AS v1
        ) t1
        CROSS JOIN ( SELECT @v2 AS v2
                   ) t2
        CROSS JOIN ( SELECT @v3 AS v3
                   ) t3

Problem: wybierz minimalną wartość stawki podaną podmiotowi Wymagania: Stawki agencji mogą być zerowe

[MinRateValue] = 
CASE 
   WHEN ISNULL(FitchRating.RatingValue, 100) < = ISNULL(MoodyRating.RatingValue, 99) 
   AND  ISNULL(FitchRating.RatingValue, 100) < = ISNULL(StandardPoorsRating.RatingValue, 99) 
   THEN FitchgAgency.RatingAgencyName

   WHEN ISNULL(MoodyRating.RatingValue, 100) < = ISNULL(StandardPoorsRating.RatingValue , 99)
   THEN MoodyAgency.RatingAgencyName

   ELSE ISNULL(StandardPoorsRating.RatingValue, 'N/A') 
END 

Zainspirowany tą odpowiedź od Nat

Jeśli używasz programu SQL Server 2005, możesz użyć funkcji UNPIVOT. Oto kompletny przykład:

create table dates 
(
  number int,
  date1 datetime,
  date2 datetime,
  date3 datetime 
)

insert into dates values (1, '1/1/2008', '2/4/2008', '3/1/2008')
insert into dates values (1, '1/2/2008', '2/3/2008', '3/3/2008')
insert into dates values (1, '1/3/2008', '2/2/2008', '3/2/2008')
insert into dates values (1, '1/4/2008', '2/1/2008', '3/4/2008')

select max(dateMaxes)
from (
  select 
    (select max(date1) from dates) date1max, 
    (select max(date2) from dates) date2max,
    (select max(date3) from dates) date3max
) myTable
unpivot (dateMaxes For fieldName In (date1max, date2max, date3max)) as tblPivot

drop table dates

Korzystanie z aplikacji CROSS APPLY (dla 2005+) ....

SELECT MostRecentDate 
FROM SourceTable
    CROSS APPLY (SELECT MAX(d) MostRecentDate FROM (VALUES (Date1), (Date2), (Date3)) AS a(d)) md

Z SQL Server 2012 możemy korzystać z IIF .

 DECLARE @Date1 DATE='2014-07-03';
 DECLARE @Date2 DATE='2014-07-04';
 DECLARE @Date3 DATE='2014-07-05';

 SELECT IIF(@Date1>@Date2,
        IIF(@Date1>@Date3,@Date1,@Date3),
        IIF(@Date2>@Date3,@Date2,@Date3)) AS MostRecentDate

Spróbuj użyć UNPIVOT:

SELECT MAX(MaxDt) MaxDt
   FROM tbl 
UNPIVOT
   (MaxDt FOR E IN 
      (Date1, Date2, Date3)
)AS unpvt;

Wolę rozwiązania oparte na przypadku, moim założeniem jest, że powinno to mieć najmniejszy wpływ na możliwy spadek wydajności w porównaniu z innymi możliwymi rozwiązaniami, takimi jak te z zastosowaniem krzyżowym, wartościami (), funkcjami niestandardowymi itp.

Oto wersja przypadku, która obsługuje wartości zerowe z większością możliwych przypadków testowych:

SELECT
    CASE 
        WHEN Date1 > coalesce(Date2,'0001-01-01') AND Date1 > coalesce(Date3,'0001-01-01') THEN Date1 
        WHEN Date2 > coalesce(Date3,'0001-01-01') THEN Date2 
        ELSE Date3
    END AS MostRecentDate
    , *
from 
(values
     (  1, cast('2001-01-01' as Date), cast('2002-01-01' as Date), cast('2003-01-01' as Date))
    ,(  2, cast('2001-01-01' as Date), cast('2003-01-01' as Date), cast('2002-01-01' as Date))
    ,(  3, cast('2002-01-01' as Date), cast('2001-01-01' as Date), cast('2003-01-01' as Date))
    ,(  4, cast('2002-01-01' as Date), cast('2003-01-01' as Date), cast('2001-01-01' as Date))
    ,(  5, cast('2003-01-01' as Date), cast('2001-01-01' as Date), cast('2002-01-01' as Date))
    ,(  6, cast('2003-01-01' as Date), cast('2002-01-01' as Date), cast('2001-01-01' as Date))
    ,( 11, cast(NULL         as Date), cast('2002-01-01' as Date), cast('2003-01-01' as Date))
    ,( 12, cast(NULL         as Date), cast('2003-01-01' as Date), cast('2002-01-01' as Date))
    ,( 13, cast('2003-01-01' as Date), cast(NULL         as Date), cast('2002-01-01' as Date))
    ,( 14, cast('2002-01-01' as Date), cast(NULL         as Date), cast('2003-01-01' as Date))
    ,( 15, cast('2003-01-01' as Date), cast('2002-01-01' as Date), cast(NULL         as Date))
    ,( 16, cast('2002-01-01' as Date), cast('2003-01-01' as Date), cast(NULL         as Date))
    ,( 21, cast('2003-01-01' as Date), cast(NULL         as Date), cast(NULL         as Date))
    ,( 22, cast(NULL         as Date), cast('2003-01-01' as Date), cast(NULL         as Date))
    ,( 23, cast(NULL         as Date), cast(NULL         as Date), cast('2003-01-01' as Date))
    ,( 31, cast(NULL         as Date), cast(NULL         as Date), cast(NULL         as Date))

) as demoValues(id, Date1,Date2,Date3)
order by id
;

a wynikiem jest:

MostRecent    id   Date1      Date2      Date3
2003-01-01    1    2001-01-01 2002-01-01 2003-01-01
2003-01-01    2    2001-01-01 2003-01-01 2002-01-01
2003-01-01    3    2002-01-01 2001-01-01 2002-01-01
2003-01-01    4    2002-01-01 2003-01-01 2001-01-01
2003-01-01    5    2003-01-01 2001-01-01 2002-01-01
2003-01-01    6    2003-01-01 2002-01-01 2001-01-01
2003-01-01    11   NULL       2002-01-01 2003-01-01
2003-01-01    12   NULL       2003-01-01 2002-01-01
2003-01-01    13   2003-01-01 NULL       2002-01-01
2003-01-01    14   2002-01-01 NULL       2003-01-01
2003-01-01    15   2003-01-01 2002-01-01 NULL
2003-01-01    16   2002-01-01 2003-01-01 NULL
2003-01-01    21   2003-01-01 NULL       NULL
2003-01-01    22   NULL       2003-01-01 NULL
2003-01-01    23   NULL       NULL       2003-01-01
NULL          31   NULL       NULL       NULL

Możesz utworzyć funkcję, w której podajesz daty, a następnie dodać funkcję do instrukcji select, jak poniżej. wybierz Numer, dbo.fxMost_Recent_Date (Date1, Date2, Date3), Koszt

create FUNCTION  fxMost_Recent_Date 

(@ Date1 smalldatetime, @ Date2 smalldatetime, @ Date3 smalldatetime) ZWRACA smalldatetime NA POCZĄTEK DEKLARACJI @Result smalldatetime

declare @MostRecent smalldatetime

set @MostRecent='1/1/1900'

if @Date1>@MostRecent begin set @MostRecent=@Date1 end
if @Date2>@MostRecent begin set @MostRecent=@Date2 end
if @Date3>@MostRecent begin set @MostRecent=@Date3 end
RETURN @MostRecent

KONIEC

W oparciu o rozwiązanie ScottPletcher z http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_24204894.html Stworzyłem zestaw funkcji (np. GetMaxOfDates3, GetMaxOfDates13), aby znaleźć max maksymalnie 13 wartości daty przy użyciu UNION ALL. Zobacz funkcję T-SQL, aby uzyskać maksimum wartości z tego samego wiersza Jednak nie rozważałem rozwiązania UNPIVOT w momencie pisania tych funkcji

Kolejny sposób użycia CASE WHEN

SELECT CASE true 
       WHEN max(row1) >= max(row2) THEN CASE true WHEN max(row1) >= max(row3) THEN max(row1) ELSE max(row3) end ELSE
       CASE true WHEN max(row2) >= max(row3) THEN max(row2) ELSE max(row3) END END
FROM yourTable

Powyżej tabeli znajduje się tabela wynagrodzeń pracowników z kolumnami wynagrodzenie1, wynagrodzenie2, wynagrodzenie3, wynagrodzenie4. Poniższe zapytanie zwróci maksymalną wartość z czterech kolumn

select  
 (select Max(salval) from( values (max(salary1)),(max(salary2)),(max(salary3)),(max(Salary4)))alias(salval)) as largest_val
 from EmployeeSalary

Uruchomienie powyższej kwerendy spowoduje wyświetlenie wyniku jako największej wartości (10001)

Logika powyższego zapytania jest następująca:

select Max(salvalue) from(values (10001),(5098),(6070),(7500))alias(salvalue)

wyjście wyniesie 10001

oto dobre rozwiązanie:

CREATE function [dbo].[inLineMax] (@v1 float,@v2 float,@v3 float,@v4 float)
returns float
as
begin
declare @val float
set @val = 0 
declare @TableVal table
(value float )
insert into @TableVal select @v1
insert into @TableVal select @v2
insert into @TableVal select @v3
insert into @TableVal select @v4

select @val= max(value) from @TableVal

return @val
end 

Nie wiem, czy jest to na SQL, itp. ... w pomocy M $ ACCESS istnieje wywołana funkcja, MAXA(Value1;Value2;...)która powinna to zrobić.

Nadzieja może komuś pomóc.

PD: Wartościami mogą być kolumny lub wartości obliczone itp.