December 28, 2016 at 1:55 am
I am having table as below which is having 3 columns. first column name is 'uid' which is primary key column.
2nd column name is 'catalog_name' which stores values as below (some values are repeated and some are single and null values)
3rd column is for last updated date and time stamp.
uid catalog_name Last_update_time
01a5a15e-fda8-45dc-b590-d92f279e8 WAAX-CWB-2016-201625 2016-03-21 21:32:00.873
01de37a5-8ebb-4c35-a31a-26f25df85 WAAX-CWB-2016-201625 2016-07-20 20:30:09.663
023e364f-061a-4fb9-99f5-161639d08 WADX-CZY-2015-DEU 2016-05-11 19:43:14.587
025cc224-9cc6-4927-83f6-a12aac9a3 WADX-CZY-2015-DEU 2016-11-09 12:09:01.303
027a753d-ab94-4b0e-b049-b9902b42 WADX-CZY-2015-DEU 2016-12-06 18:59:12.540
02b615b9-6944-4b43-bfe6-b017ab49 WAEX-TTG-2012-201725 2016-09-21 11:57:22.637
02ce8663-72c7-4259-ab90-87c856ff3 NULL 2016-03-06 22:31:39.710
00042e5d-d4e1-49f8-b2d3-a9778262 NULL 2016-08-15 18:46:43.803
00432cd2-3d46-4f6b-95ff-12776c4e1 NULL 2016-03-17 21:33:59.120
I'm using the below query:
"select catalog_name, CatalogCount, Last_update_time from
(
select catalog_name, Last_update_time, COUNT(*) over (partition by catalog_name) AS CatalogCount from [dbo].[catalog]
) TheResult
group by catalog_name, CatalogCount, Last_update_time having CatalogCount >1 order by catalog_name, Last_update_time desc"
and the Result is as below:
It displayed catalog names and it's count and its recent updated time (in ascending order).
catalog_name CatalogCount Last_update_time
WAAX-CWB-2016-201625 2 2016-07-20 20:30:09.663
WAAX-CWB-2016-201625 2 2016-03-21 21:32:00.873
WADX-CZY-2015-DEU 3 2016-12-06 18:59:12.540
WADX-CZY-2015-DEU 3 2016-11-09 12:09:01.303
WADX-CZY-2015-DEU 3 2016-05-11 19:43:14.587
WAEX-TTG-2012-201725 1 2016-09-21 11:57:22.637
NULL 3 2016-08-15 18:46:43.803
NULL 3 2016-03-17 21:33:59.120
NULL 3 2016-03-06 22:31:39.710
But, I want the result as in below table:
For catalog_name: WAAX-CWB-2016-201625, with this name, there are 2 catalogs available in table with different last updated timings. i want to retain only one catalog which is updated recently/ with latest updated time from these 2 catalogs. and want to remove other one with older last updated time (in below table, I just did strike through)
For catalog_name: WADX-CZY-2015-DEU, with this name, there are 3 catalogs available in table with different last updated timings. Want to retain only one row/one catalog which is having latest last updated time from these 3 catalogs. and want to remove other 2 catalogs with older last updated time (in below table, I just did strike through).
For catalog_name: NULL, with this name, there are 3 catalogs available in table with different last updated timings. Want to retain only one row/one catalog which is having latest last updated time from these 3 catalogs. and want to remove other 2 catalogs with older last updated time (in below table, I just did strike through).
catalog_name CatalogCount Last_update_time
WAAX-CWB-2016-201625 2 2016-07-20 20:30:09.663
WAAX-CWB-2016-201625 2 2016-03-21 21:32:00.873
WADX-CZY-2015-DEU 3 2016-12-06 18:59:12.540
WADX-CZY-2015-DEU 3 2016-11-09 12:09:01.303
WADX-CZY-2015-DEU 3 2016-05-11 19:43:14.587
WAEX-TTG-2012-201725 1 2016-09-21 11:57:22.637
NULL 3 2016-08-15 18:46:43.803
NULL 3 2016-03-17 21:33:59.120
NULL 3 2016-03-06 22:31:39.710
December 28, 2016 at 2:08 am
SQLserver_learner (12/28/2016)
I attached word doc with all my requirement. I need assistance to figure out the right query based on my requirement. Thanks in advance..
Hi and welcome to the forum. This is a simple problem to handle but you'll need to do a little bit of work in order for us to help you: post the DDL (create table) script, sample data as an insert statement and the expected results.
😎
Although the requirements are clear in the document, many here will not even open such an attachment nor have time to create the sample data set necessary for demonstrating a solution.
December 28, 2016 at 2:19 am
Thanks for the response. So, where can I post my requirement? Bcoz I need it. Thanks in advance..
December 28, 2016 at 2:26 am
SQLserver_learner (12/28/2016)
Thanks for the response. So, where can I post my requirement? Bcoz I need it. Thanks in advance..
Simply copy and paste from the document and then use the IFCodes for the code to aid the readability. Result sets are best handles in fixed width format in "plain" IFCode.
😎
December 28, 2016 at 9:49 am
You're overcomplicating things. There is no reason to use windowed functions here. You want a simple aggregate.
-- original code with windowed functions
select catalog_name, CatalogCount, Last_update_time from
(
select catalog_name, Last_update_time, COUNT(*) over (partition by catalog_name) AS CatalogCount from [dbo].[catalog]
) TheResult
group by catalog_name, CatalogCount, Last_update_time having CatalogCount >1 order by catalog_name, Last_update_time desc
-- updated code with simple aggregates
SELECT catalog_name, COUNT(*) AS catalog_count, MAX(last_update_time) AS last_update_time
FROM dbo.Catalog
GROUP BY catalog_name
Windowed functions are used when you need to mix individual details with group information. Here, you are only interested in the information about the group as a whole, so there is no need to use windowed functions.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 29, 2016 at 3:04 am
Hi Drew Allen,
Thank you very much for the answer. Sorry for my previous question. Now, again, i posted my requirement as below with DDL, DML. Please assist to get exact query. Thanks in advance
Table from database is as followed below
DDL:
CREATE TABLE [dbo].[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]
) 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
December 29, 2016 at 3:36 am
Not sure how we're going to get you the exact query when you haven't posted the required results based on the sample data. The CREATE TABLE and INSERT statements don't work properly - you clearly didn't test them before posting. Never mind, I've tidied them up for you as below. If you don't require the MDDS01_UUI_D column in your results then Drew is right - you only need a simple GROUP BY query. If you do require that column, you can use my query below.
JohnCREATE TABLE [dbo].[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]
) ON [PRIMARY];
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');
WITH LatestDates AS (
SELECT
MDDS01_UUI_D
,MDDS01_PORTFO_D
,MDDS01_LAST_UPDT_TIME
,ROW_NUMBER() OVER (PARTITION BY MDDS01_PORTFO_D ORDER BY MDDS01_LAST_UPDT_TIME DESC) AS RowNo
,COUNT(*) OVER (PARTITION BY MDDS01_PORTFO_D) AS NoofRows
FROM MMDDS01_PORTFO_LIST
)
SELECT
MDDS01_UUI_D
,MDDS01_PORTFO_D
,MDDS01_LAST_UPDT_TIME
,NoofRows
FROM LatestDates
WHERE RowNo = 1
December 29, 2016 at 4:04 am
Simple row_number solution (didn't see John's fine solution before posting)
😎
USE TEEST;
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'dbo.MMDDS01_PORTFO_LIST') IS NOT NULL DROP TABLE dbo.MMDDS01_PORTFO_LIST;
CREATE TABLE [dbo].[MMDDS01_PORTFO_LIST]
(
[MDDS01_UUI_D] [varchar](64) NOT NULL CONSTRAINT [MMDDS01_PK] PRIMARY KEY CLUSTERED
,[MDDS01_PORTFO_D] [nvarchar](64) NULL
,[MDDS01_LAST_UPDT_TIME] [datetime] NOT NULL
);
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')
;
;WITH BASE_DATA AS
(
SELECT
MPL.MDDS01_UUI_D
,MPL.MDDS01_PORTFO_D
,MPL.MDDS01_LAST_UPDT_TIME
,ROW_NUMBER() OVER
(
PARTITION BY MPL.MDDS01_PORTFO_D
ORDER BY MPL.MDDS01_LAST_UPDT_TIME DESC
) AS RID
FROM dbo.MMDDS01_PORTFO_LIST MPL
)
SELECT
BD.MDDS01_UUI_D
,BD.MDDS01_PORTFO_D
,BD.MDDS01_LAST_UPDT_TIME
FROM BASE_DATA BD
WHERE BD.RID = 1;
Output
MDDS01_UUI_D MDDS01_PORTFO_D MDDS01_LAST_UPDT_TIME
---------------------------------- ----------------------- -----------------------
46671907-9eb5-4a2f-bf6b-1e3750902 NULL 2016-12-17 16:41:32.807
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
6913e1a5-5f0b-4c31-9d41-22e3df975 WAEX-CB2-2010-B-201600 2016-12-01 18:53:38.893
02b615b9-6944-4b43-bfe6-b017ab49 WAEX-TTG-2012-201725 2016-09-21 11:57:22.637
a8a71e9a-77d6-400c-80de-08fd3a903 WAIX-CCN-2012-201650 2016-12-12 20:35:04.830
Note: the sample data set previously posted is misleading as it has string values of "NULL" instead of NULL and some leading spaces.
December 29, 2016 at 4:05 am
Thank you John.
Both the queries are working. but it is giving 2 rows instead 1 row for NULL values for column [MDDS01_PORTFO_D]. one row is having latest timestamp as expected. 2nd row is having older time stamp. Is there a solution for this?
As I mentioned earlier, i want to retain the rows that we get from this query and rest want to remove from table. So, do we have to prepare a delete query also seperately ?? or can we use delete in your query itself ???..
If you could go through below steps that I already shared in my previous post::
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
December 29, 2016 at 4:14 am
we have are having total 1600 NULL value rows. I could not share all these in insert statement to you. so I gave 5 NULL rows. becoz of more NULL , am i getting 2 NULL rows in result?? Thanks
December 29, 2016 at 4:20 am
SQLserver_learner (12/29/2016)
Thank you John.Both the queries are working. but it is giving 2 rows instead 1 row for NULL values for column [MDDS01_PORTFO_D]. one row is having latest timestamp as expected. 2nd row is having older time stamp. Is there a solution for this?
As I mentioned earlier, i want to retain the rows that we get from this query and rest want to remove from table. So, do we have to prepare a delete query also seperately ?? or can we use delete in your query itself ???..
If you could go through below steps that I already shared in my previous post::
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
Use the sample set I or John posted, your sample data is flawed as it has "NULL" character string instead of NULL and some leading spaces, both of which contributes to the "strange" results you are getting.
😎
December 29, 2016 at 4:28 am
SQLserver_learner (12/29/2016)
i want to retain the rows that we get from this query and rest want to remove from table. So, do we have to prepare a delete query also seperately ?? or can we use delete in your query itself ???..
This should do the delete for you:WITH LatestDates AS (
SELECT
MDDS01_UUI_D
,MDDS01_PORTFO_D
,MDDS01_LAST_UPDT_TIME
,ROW_NUMBER() OVER (PARTITION BY MDDS01_PORTFO_D ORDER BY MDDS01_LAST_UPDT_TIME DESC) AS RowNo
FROM MMDDS01_PORTFO_LIST
)
DELETE FROM MMDDS01_PORTFO_LIST
WHERE MDDS01_UUI_D NOT IN (
SELECT MDDS01_UUI_D
FROM LatestDates
WHERE RowNo = 1
)
If you could go through below steps that I already shared in my previous post::
I'm afraid I don't have the time or patience for that. If what Eirikur said doesn't resolve the issue, please post the results that you expect from your sample data.
John
December 29, 2016 at 5:22 am
Did you got what I posted??? Thanks
December 29, 2016 at 5:24 am
sorry.. pls ignore..
thank you very much for the delete script.. i'm just testing it ..will come back.. thanks you once again
December 29, 2016 at 6:48 am
apologies.. pls ignore my previous post about NULL comment.. Thanks
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply