SQLTeam.com | Weblogs | Forums

Query to fetch records with latest last updated time


#1

i want to draw tables . But i can not draw as the editor not supported here in Create new Topic message. Also, new user can not upload file. Please assist how can I post my question. Thanks


#2

do the following
show us DDL

create table dbo.FetchRecords(widget int, frufrah varchar(35), playaplaya bit, booya datetime)

DML

insert into FetchRecords
select 1, 'Rogue Uno', 0, getdate() union
select 1, 'Mann Behind Short Castle', 1, getdate() + 12

#3

I'm posting all the table details and my requirement. Please assist me in sharing the exact query. Thanks in advance

Table from database is as followed below:

DDL:

CREATE TABLE [dbo].[MMDDS01_PORTFO_LIST](
[MDDS01_UUI_D] varchar NOT NULL,
[MDDS01_PORTFO_D] nvarchar NULL,
[MDDS01_LAST_UPDT_TIME] [datetime] NOT NULL,

CONSTRAINT [MMDDS01_PK] PRIMARY KEY CLUSTERED
(
[MDDS01_UUI_D] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

DML:

INSERT INTO [dbo].[MMDDS01_PORTFO_LIST]
([MDDS01_UUI_D]
,[MDDS01_PORTFO_D]
,[MDDS01_LAST_UPDT_TIME])
VALUES
(<MDDS01_UUI_D, varchar(64),>
,<MDDS01_PORTFO_D, nvarchar(64),>
,<MDDS01_LAST_UPDT_TIME, datetime,>)

Insert Query:

INSERT INTO [dbo].[MMDDS01_PORTFO_LIST] values
(‘02ce8663-72c7-4259-ab90-87c856ff3’,' NULL', ‘2016-03-06 22:31:39.710’),
(‘01a5a15e-fda8-45dc-b590-d92f279e8’,' WAAX-CWB-2016-201625', ‘2016-03-21 21:32:00.873’),
(‘023e364f-061a-4fb9-99f5-161639d08’,'WADX-CZY-2015-DEU', ‘2016-05-11 19:43:14.587’),
(‘02b615b9-6944-4b43-bfe6-b017ab49’,' WAEX-TTG-2012-201725', ‘2016-09-21 11:57:22.637’),
(‘00042e5d-d4e1-49f8-b2d3-a9778262’,' NULL', ‘2016-08-15 18:46:43.803’),
(‘025cc224-9cc6-4927-83f6-a12aac9a3’,'WADX-CZY-2015-DEU', ‘2016-11-09 12:09:01.303’),
(‘01de37a5-8ebb-4c35-a31a-26f25df85’,'WAAX-CWB-2016-201625', ‘2016-07-20 20:30:09.663’),
(‘027a753d-ab94-4b0e-b049-b9902b42’,'WADX-CZY-2015-DEU', ‘2016-12-06 18:59:12.540’),
(‘00432cd2-3d46-4f6b-95ff-12776c4e1’,'NULL', ‘2016-03-17 21:33:59.120’),
(‘a8a71e9a-77d6-400c-80de-08fd3a903’,'WAIX-CCN-2012-201650', ‘2016-12-12 20:35:04.830’),
(‘46671907-9eb5-4a2f-bf6b-1e3750902’,'NULL', ‘2016-12-17 16:41:32.807’),
(‘c2380517-4f9f-4a11-9012-53ebd1113’, ‘WAIX-CCN-2012-201650', ‘2016-12-09 12:05:49.643’),
(‘1381c3df-763a-47a8-947c-ebb31b2f1’,'NULL', ‘2016-11-28 13:17:26.000’),
(‘a029e35b-e6e3-482d-b834-3bf95b3ad’,'WAIX-CCN-2012-201650', ‘2016-12-09 04:12:40.267’),
(‘d10e6c6c-b172-435a-9ea2-1ac29c7ea’,'WAIX-CCN-2012-201650', ‘2016-12-09 11:41:00.670’),
(‘6913e1a5-5f0b-4c31-9d41-22e3df975’,'WAEX-CB2-2010-B-201600', ‘2016-12-01 18:53:38.893’),
(‘4407bf04-62d9-4f2d-a364-a31a751de’,'WAEX-CB2-2010-B-201600', ‘2016-11-30 15:26:45.253’),
(‘8c268304-181e-4f86-92ff-f26cacb3df’,'WAEX-CB2-2010-B-201600', ‘2016-11-30 17:44:55.827’),
(‘9f9cca48-a065-486d-944a-8e32d4cc6’,'WAEX-CB2-2010-B-201600', ‘2016-12-01 18:12:18.520’),
(‘04eb6b53-60fd-482a-9133-db549df38’,'WAEX-CB2-2010-B-201600', ‘2016-12-01 11:37:34.750’),

My requirement is as followed below:

for the MDDS01_PORTFO_D column, for value WAAX-CWB-2016-201625, we have 2 rows. From these 2 rows, I want, only one row has to be retained which is having latest timestamp of column ‘MDDS01_LAST_UPDT_TIME’ and rest of catalogs with older dates are needs to be deleted

for the MDDS01_PORTFO_D column, for value WADX-CZY-2015-DEU, we have 3 rows. From these 3 rows, I want, only one row has to be retained which is having latest timestamp of column ‘MDDS01_LAST_UPDT_TIME’ and rest of catalogs with older dates are needs to be deleted

for the MDDS01_PORTFO_D column, for value WAEX-TTG-2012-201725, we have 1 row. I want, this one row has to be retained as there is only this row alone available for this portfolio

for the MDDS01_PORTFO_D column, for value NULL, we have 5 rows. From these 5 rows, I want, only one row has to be retained which is having latest timestamp of column MDDS01_LAST_UPDT_TIME’ and rest of catalogs with older dates are needs to be deleted

for the MDDS01_PORTFO_D column, for value WAIX-CCN-2012-201650, we have 4 rows. From these 5 rows, I want, only one row has to be retained which is having latest timestamp of column MDDS01_LAST_UPDT_TIME’ and rest of catalogs with older dates are needs to be deleted

for the MDDS01_PORTFO_D column, for value WAEX-CB2-2010-B-201600, we have 5 rows. From these 5 rows, I want, only one row has to be retained which is having latest timestamp of column MDDS01_LAST_UPDT_TIME’ and rest of catalogs with older dates are needs to be deleted


#4

Hi yosiasz, any luck ??


#5

Hi .. this is what i got .. is this what you are looking for

SET statistics time, io ON; 

IF Object_id('tempdb.dbo.#MMDDS01_PORTFO_LIST', 'U') IS NOT NULL 
  DROP TABLE #MMDDS01_PORTFO_LIST; 

CREATE TABLE #mmdds01_portfo_list 
  ( 
     [mdds01_uui_d]          [VARCHAR](64) NOT NULL, 
     [mdds01_portfo_d]       [NVARCHAR](64) NULL, 
     [mdds01_last_updt_time] [DATETIME] NOT NULL, 
     CONSTRAINT [MMDDS01_PK] PRIMARY KEY CLUSTERED ( [mdds01_uui_d] ASC )WITH ( 
     pad_index = OFF, statistics_norecompute = OFF, ignore_dup_key = OFF, 
     allow_row_locks = on, allow_page_locks = on) ON [PRIMARY] 
  ) 


INSERT INTO #MMDDS01_PORTFO_LIST VALUES 
('02ce8663-72c7-4259-ab90-87c856ff3' , 'NULL'                  , '2016-03-06 22:31:39.710'),
('01a5a15e-fda8-45dc-b590-d92f279e8' , 'WAAX-CWB-2016-201625'  , '2016-03-21 21:32:00.873'),
('023e364f-061a-4fb9-99f5-161639d08' , 'WADX-CZY-2015-DEU'     , '2016-05-11 19:43:14.587'),
('02b615b9-6944-4b43-bfe6-b017ab49'  , 'WAEX-TTG-2012-201725'  , '2016-09-21 11:57:22.637'),
('00042e5d-d4e1-49f8-b2d3-a9778262'  , 'NULL'                  , '2016-08-15 18:46:43.803'),
('025cc224-9cc6-4927-83f6-a12aac9a3' , 'WADX-CZY-2015-DEU'     , '2016-11-09 12:09:01.303'),
('01de37a5-8ebb-4c35-a31a-26f25df85' , 'WAAX-CWB-2016-201625'  , '2016-07-20 20:30:09.663'),
('027a753d-ab94-4b0e-b049-b9902b42'  , 'WADX-CZY-2015-DEU'     , '2016-12-06 18:59:12.540'),
('00432cd2-3d46-4f6b-95ff-12776c4e1' , 'NULL'                  , '2016-03-17 21:33:59.120'),
('a8a71e9a-77d6-400c-80de-08fd3a903' , 'WAIX-CCN-2012-201650'  , '2016-12-12 20:35:04.830'),
('46671907-9eb5-4a2f-bf6b-1e3750902' , 'NULL'                  , '2016-12-17 16:41:32.807'),
('c2380517-4f9f-4a11-9012-53ebd1113' , 'WAIX-CCN-2012-201650'  , '2016-12-09 12:05:49.643'),
('1381c3df-763a-47a8-947c-ebb31b2f1' , 'NULL'                  , '2016-11-28 13:17:26.000'),
('a029e35b-e6e3-482d-b834-3bf95b3ad' , 'WAIX-CCN-2012-201650'  , '2016-12-09 04:12:40.267'),
('d10e6c6c-b172-435a-9ea2-1ac29c7ea' , 'WAIX-CCN-2012-201650'  , '2016-12-09 11:41:00.670'),
('6913e1a5-5f0b-4c31-9d41-22e3df975' , 'WAEX-CB2-2010-B-201600', '2016-12-01 18:53:38.893'),
('4407bf04-62d9-4f2d-a364-a31a751de' , 'WAEX-CB2-2010-B-201600', '2016-11-30 15:26:45.253'),
('8c268304-181e-4f86-92ff-f26cacb3df', 'WAEX-CB2-2010-B-201600', '2016-11-30 17:44:55.827'),
('9f9cca48-a065-486d-944a-8e32d4cc6' , 'WAEX-CB2-2010-B-201600', '2016-12-01 18:12:18.520'),
('04eb6b53-60fd-482a-9133-db549df38' , 'WAEX-CB2-2010-B-201600', '2016-12-01 11:37:34.750')

SELECT mdds01_portfo_d, 
       Max(mdds01_last_updt_time) 
FROM   #mmdds01_portfo_list 
GROUP  BY mdds01_portfo_d 

SET statistics time, io OFF; 


#6

Thank you very much for the script given..
Yes, this looks good.

here, we are getting only one NULL row from the input that i gave to you. That is as per desired.

when i run this query in original DB, i'm getting 2 NULL value rows. one is having latest timestamp as desired. another is having older date which is not needed. I don't understand why one additional row is getting added for NULL. Remaining output is fine.

But, in the table, i have total 1600+ rows for column 'mdds01_portfo_d' that has NULL value. I provided you only 5 NULL rows in insert script as it is difficult to arrange 1600+ rows in insert query.

Also, I need [mdds01_uui_d] column also in result..
also, as I said in my requirement, i need to keep one row only which has latest timestamp and want to delete remaining. this has to be same for every mdds01_portfo_d. Thanks


#7
Hi 

The reason why you are getting two null values 

Example Data: 

Col1   Col2   Col3 
1           3      8
2           3     10

when you select 
SELECT col2,max(col3)  group by col2 

u will get 

col2 col3
3      10

when you include col1 in the select 
SELECT col1,col2,max(col3) group by col1,col2

u will get 

col1 col2 col3 
1       3     8
2      3     10

this is because adding col1 it has two different values 
col1
1
2

if col1 had the same values then it would be like 
col1 
1 
1 

SELECT col1,col2,max(col3) group by col1,col2
the results would be 
col1 col2 col3 
1     3       10

u r seeing the difference 
Data 
col1 col2 col3 
1       3     8
2      3     10

col1
1
2 

query SELECT col1,col2,max(col3) group by col1,col2
col1 col2 col3 
1      3     8
2      3    10 


if the data in col1 is the same 
col1
1
1 

query SELECT col1,col2,max(col3) group by col1,col2
will give 
col1 col2 col3 
1      3      10 


Let me if you understand the concept 

I can make it even more clear for you 
to understand

#8

My apologies .. pls ignore the above post for the comments of NULL issue.. could you pls look into the below alone..

Also, I need [mdds01_uui_d] column also in result..
also, as I said in my requirement, i need to keep one row only which has latest timestamp and want to delete remaining. This has to be same for each mdds01_portfo_d. pls share one select and one delete query be adding [mdds01_uui_d] column. Thanks in advance


#9

Hi Please if the following works

SELECT Count(*) 
FROM   #mmdds01_portfo_list 

DELETE FROM #mmdds01_portfo_list 
WHERE  [mdds01_uui_d] + mdds01_portfo_d 
       + Cast([mdds01_last_updt_time] AS VARCHAR(100)) NOT IN 
              (SELECT [mdds01_uui_d] + mdds01_portfo_d + maxdt 
               FROM   (SELECT [mdds01_uui_d], 
                              mdds01_portfo_d, 
                              Cast(Max([mdds01_last_updt_time]) AS VARCHAR(100)) 
                              maxdt 
                       FROM   #mmdds01_portfo_list 
                       GROUP  BY [mdds01_uui_d], 
                                 mdds01_portfo_d) b) 

SELECT Count(*) 
FROM   #mmdds01_portfo_list

#10

Hi harishgg1, Thank you very much for delete query.. how can I ensure which records will get delete by this query? I want to verify data before deleting. Could you please comment delete statement in the script and add select statement to know which records it will fetch. after verifying i'll uncomment delete and comment select statement. Thanks


#11

Hi Please see if this works

-- select max date time records into temp table 
SELECT [mdds01_uui_d], 
       mdds01_portfo_d, 
       Max([mdds01_last_updt_time])AS maxdt 
INTO   #xyz 
FROM   #mmdds01_portfo_list 
GROUP  BY [mdds01_uui_d], 
          mdds01_portfo_d 

-- select statement to see what records will be deleted 
SELECT a.* 
FROM   #mmdds01_portfo_list a 
       JOIN #xyz b 
         ON a.mdds01_portfo_d = b.mdds01_portfo_d 
            AND a.mdds01_uui_d = b.mdds01_uui_d 
            AND a.mdds01_last_updt_time <> b.maxdt 

-- delete statement to delete the records 
DELETE a 
FROM   #mmdds01_portfo_list a 
       JOIN #xyz b 
         ON a.mdds01_portfo_d = b.mdds01_portfo_d 
            AND a.mdds01_uui_d = b.mdds01_uui_d 
            AND a.mdds01_last_updt_time <> b.maxdt

#12

this query is fetching zero rows. Thanks


#13

The below SELECT query is working.. could you pls share the DELETE query also.. Thanks

SELECT mdds01_portfo_d,
Max(mdds01_last_updt_time)
FROM #mmdds01_portfo_list
GROUP BY mdds01_portfo_d