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
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() + 12I'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
Hi yosiasz, any luck ??
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;
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
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 understandMy 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
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_listHi 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
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.maxdtthis query is fetching zero rows. Thanks
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