How to get max date for each column in SQL Server
-
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.
So my output would be like below.
Could any one please help how to write query for this
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.
-
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 -
Hi ,
I have added the insert update code in main question
-
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 -
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
-
Your device name column must contain some strings which are not in the format
<make>_<name>_<date>_<time> <am or pm>
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 -
Hi Phil Parkin,
Yes some rows my device names doesnt have the underscore.
Could you please provide an alternate to ignore these
-
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 - 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) -
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 1SQL 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".
-
This was removed by the editor as SPAM
-
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