Find MAX value in two columns simultaneously in SQL table-CodePudding

Can somebody help? I have this table (simplified view, it has 20 columns and more then 1 mln rows)

date_id     period_time PlotsID dateOfContractID
20071225    1:12:45     212     a12f
20080812    1:12:45     212     a12f
20080815    10:07:46    232     f45j
20100213    8:05:12     435     y54g
20100213    8:06:33     435     y54g

And I need to find all data with MAX date_id and MAX period_time, grouped by PlotsID And it must be this

date_id      period_time    PlotsID  dateOfContractID
20080812     1:12:45        212      a12f
20080815     10:07:46       232      f45j
20100213     8:06:33        435      y54g

Using this code, I found MAX date_id and it's working correct, but I need after Max date find MAx Period_time, OR find in one step

SELECT 
    [date_id],
    [period_time],
    [PlotsID],
    [FieldID],
    [partnerContract],
    [ownerContractID],
    [partnerContractCode]
    
FROM
    bd 
WHERE
    EXISTS(
        SELECT
            1 AS Expr1
        FROM
            bd AS t2
        WHERE
            partnerContractCode = bd.partnerContractCode                             
        GROUP BY
            partnerContractCode
        HAVING
            ( bd.date_id = MAX(date_id) )
    )
) AS t1

Create TABLE

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE bd
(   [hash_diff] [varbinary](8000)  NOT NULL,
    [hash_key] [varbinary](8000)  NULL,
    [date_id] [int]  NULL,
    [period_time] [time](7)  NULL,
    [PlotsID] [bigint]  NULL,
    [FieldID] [bigint]  NULL,
    [partnerContract] [nvarchar](100)  NULL,
    [ownerContractID] [bigint]  NULL,
    [partnerContractCode] [nvarchar](50)  NULL,
    
)
WITH
(
    DISTRIBUTION = HASH ( [hash_key] ),
    CLUSTERED INDEX 
    (
        [date_id] ASC
    )
)
GO

CodePudding user response：

Try this

DECLARE @tbl_SampleData AS TABLE ( date_id DATE, period_time varchar(8), plotsID INT, dateOfContractID VARCHAR(10))

INSERT INTO @tbl_SampleData (date_id , period_time , plotsID , dateOfContractID )
VALUES 
('20071225',    '1:12:45',     212,     'a12f'),
('20080812',    '1:12:45',     212,     'a12f'),
('20080815',    '10:07:46',    232,     'f45j'),
('20100213',    '8:05:12',     435,     'y54g'),
('20100213',    '8:06:33',     435,     'y54g')

--SELECT * FROM @tbl_SampleData


SELECT date_id, period_time, plotsID,  dateOfContractID FROM 
(
SELECT date_id, period_time, plotsID,  dateOfContractID ,
ROW_NUMBER() OVER(PARTITION BY PlotsID ORDER BY date_id DESC, period_time DESC) AS RowID
FROM @tbl_SampleData
) AS a
WHERE RowID = 1