Find MAX value in two columns simultaneously in SQL table

Question

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

Hint: combine `date_id` (which should be a `date` column...) with `period_time` (which should be a `time` column) into a single `datetime2(7)` column which you can then use in a single `MAX()` aggregate. — Dai, Oct 03 '22 at 12:33
We need your `CREATE TABLE` statement so we can see the exact data-types of each column, their nullability, and other metadata. — Dai, Oct 03 '22 at 13:05
_Yikes_ - there's quite a bit of bad-design and anti-patterns in that `db` table - are you able to redesign it at all? — Dai, Oct 03 '22 at 13:34
Look at using cross apply and top 1 order by your date, time desc. Example: https://stackoverflow.com/questions/1139160/when-should-i-use-cross-apply-over-inner-join — xQbert, Oct 03 '22 at 13:38
Wait... are you just getting the max time for each day then joining back to your base set? — xQbert, Oct 03 '22 at 13:42

DouadyRabbit · Accepted Answer · 2022-10-03T14:52:28.370

0

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

edited Oct 03 '22 at 14:52

answered Oct 03 '22 at 14:03

DouadyRabbit

118
7

1

the code returns the minimum value instead of the maximum – Dmytro1988 Oct 03 '22 at 14:38
True, missed DESC on date_id, thanks @Dmytro1988 – DouadyRabbit Oct 03 '22 at 14:52

Find MAX value in two columns simultaneously in SQL table

1 Answers1