How to get max date for each column in SQL Server

Question

Post reply

How to get max date for each column in SQL Server

Krish319

Old Hand

Points: 352

August 23, 2023 at 3:41 pm

Go to Answer

#4269261

Hello All,

I have a table where I am having data like below.

Here my requirement is I need to get the device names which has the max sync date.

Screenshot 2023-08-23 210455

So my output would be like below.

Could any one please help how to write query for this

Screenshot 2023-08-23 210455

Here is my data :

create table #test1
(syncdate date,
updateddate date,
devicename varchar(500))
insert into #test1 values('7/17/2023','2/5/2023','HP_AndroidForWork_2/5/2023_2:08 AM'),
('8/23/2023','7/17/2023','HP_AndroidForWork_7/17/2023_11:01 PM'),
('7/17/2023','7/17/2023','HP_AndroidForWork_7/17/2023_7:44 PM'),
('8/23/2023','7/17/2023','Dell_AndroidForWork_7/17/2023_11:01 PM'),
('7/17/2023','7/17/2023','Dell_AndroidForWork_7/17/2023_7:44 PM'),
('7/17/2023','2/5/2023','Dell_AndroidForWork_2/5/2023_2:08 AM'),
('7/17/2023','2/5/2023','Lenovo_AndroidForWork_2/5/2023_2:08 AM'),
('8/23/2023','7/17/2023','Lenovo_AndroidForWork_7/17/2023_11:01 PM'),
('7/17/2023','7/17/2023','Lenovo_AndroidForWork_7/17/2023_7:44 PM')

This topic was modified 1 year, 3 months ago by Krish319.

Phil Parkin

SSC Guru

Points: 246960
More actions
August 23, 2023 at 4:32 pm
Answer
#4269302
Does this work for you? Not quite what you asked for, but maybe even an improvement.
```
SELECT DeviceName = SUBSTRING (t.devicename, 1, c2.p2 - 1)
      ,syncdate = MAX (t.syncdate)
      ,updateddate = MAX (t.updateddate)
FROM #test1 t
    CROSS APPLY
(SELECT p1 = CHARINDEX ('_', t.devicename)) c1
    CROSS APPLY
(SELECT p2 = CHARINDEX ('_', t.devicename, c1.p1 + 1)) c2
GROUP BY SUBSTRING (t.devicename, 1, c2.p2 - 1);
```
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

August 24, 2023 at 8:01 am

This was removed by the editor as SPAM
October 30, 2023 at 7:37 pm

This was removed by the editor as SPAM

Viewing 12 posts - 1 through 11 (of 11 total)

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Please provide your test data in consumable form (ie, with CREATE TABLE / INSERT statements), so that we can paste into SSMS and then help you with your code.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Krish319 Old Hand Points: 352 More actions · Answer 2

Hi ,

I have added the insert update code in main question

Krish319 Old Hand Points: 352 More actions · Answer 3

Hi Phil Parkin,

This is working on the sample data I created.

But If I apllied on my original table I am getting below error.

Invalid length parameter passed to the LEFT or SUBSTRING function.

Could you please help

Phil Parkin SSC Guru Points: 246960 More actions · Answer 4

Your device name column must contain some strings which are not in the format

My code will error unless there are two or more underscores in the device name.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Krish319 Old Hand Points: 352 More actions · Answer 5

Hi Phil Parkin,

Yes some rows my device names doesnt have the underscore.

Could you please provide an alternate to ignore these

Ed B Hall of Fame Points: 3666 More actions · Answer 6

Do you want the devices with the max syncdate, or the max syncdate per device ? Your test data returns the same either way.

The max syncdate is 2023-08-23. If you want all the devices that have that syncdate this might work. RANK would do just as well. If you need the max sync date per device then you'll need to substring the device names and max the syncdate as outlined above.

SELECT  b.syncdate,
        b.updateddate,
        b.devicename
FROM (
        SELECT  a.syncdate,
                a.updateddate,
                a.devicename,
                DENSE_RANK() OVER (ORDER BY a.syncdate DESC) AS rn
        FROM #test1 AS a
    ) AS b
WHERE b.rn = 1

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 7

Krish319 wrote:

Hi Phil Parkin,
This is working on the sample data I created.
But If I apllied on my original table I am getting below error.
Invalid length parameter passed to the LEFT or SUBSTRING function.
Could you please help

You'll need to post the data that caused the error, to be sure. It'll be something that has a different pattern in the devicename column than what your test data has.

--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.

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

ScottPletcher SSC Guru Points: 101095 More actions · Answer 8

SELECT LEFT(devicename, month_in_date - 2) AS device, MAX(CAST(devicedate AS datetime)) AS max_device_date
FROM #test1
CROSS APPLY (
    SELECT PATINDEX('%[0-9]/%', devicename) AS month_in_date_prelim
) AS ca1
CROSS APPLY (
    SELECT CASE WHEN SUBSTRING(devicename, month_in_date_prelim -1, 1) LIKE '[0-9]' THEN month_in_date_prelim - 1 ELSE month_in_date_prelim END AS month_in_date
) AS ca2
CROSS APPLY (
    SELECT /*CAST(*/REPLACE(SUBSTRING(devicename, month_in_date - 1, 20), '_', ' ') /*AS datetime*/ AS devicedate
) AS ca3
GROUP BY LEFT(devicename, month_in_date - 2)
ORDER BY 1

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".