Need SQL Query for unique constraint 2 columns take all Min date except Max Date

Question

Need SQL Query for unique constraint 2 columns take all Min date except Max Date

Indudhar Gowda 426

Need SQL Query for unique constraint 2 columns take all Min date and do not take Max Date.

In the Above Screen Shot.....Need SQL Query for [reportableresultcode] and [iscalculated] are unique constraint 2 columns, Take duplicates but dont take MAX Date.

Insert Duplicate Row's in TempTable.

I have attached the Excel sheet ..

Olaf Helper 47,436 Reputation points

2022-06-02T05:41:17.307+00:00

In the Above Screen Shot

We can't query screenshot to test it, so please post table design as DDL, some sample data as DML statement and the expected result.

Take duplicates but dont take MAX Date.

I don't understand a single word of that statement; may can you explain it more detailed, please.
Bert Zhou-msft 3,436 Reputation points

2022-06-02T05:52:45.517+00:00

Hi,@Indudhar Gowda

When you post the question, you should tell us more about the relationship between the three columns , how is the identification between the max and min dates ? Looking forward to more information from you.

Bert Zhou
Indudhar Gowda 426 Reputation points

2022-06-02T06:24:45.887+00:00

@Olaf Helper : Sorry Forgot to Upload Excel Sheet...

https://1drv.ms/x/s!Ag8QU6ar3yRugYxS_U4hYy43yNj_zg?e=DnH4Ws
Indudhar Gowda 426 Reputation points

2022-06-02T06:29:40.763+00:00

[reportableresultcode] and [iscalculated] are unique constraint 2 columns..

alter table [ReportableResults] add constraint Unique_ReportableResults_ReportableResultCode_IsCalculated unique (ReportableResultCode, IsCalculated);
Bert Zhou-msft 3,436 Reputation points

2022-06-02T06:34:44.237+00:00

Please post table design as DDL instead of excel sheet.
Indudhar Gowda 426 Reputation points

2022-06-02T06:50:03.393+00:00

Database is Huge and there are Multiple Columns have lot of Dependency....Just to look everything easy I have Given the dummy Data...
Bjoern Peters 8,921 Reputation points

2022-06-02T07:12:52.327+00:00

We would like to help you with your query, but we need to have at least to table definition of the source data table...

are those columns all nvarchar(max) or is there a uniqueidentifier or date column.... we don't really care about constraint... just the source DDL and ideally the data as INSERTs (not all rows but 10-20 rows)
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2022-06-02T08:48:07.33+00:00

[reportableresultcode] and [iscalculated] are unique constraint 2 columns..

>

alter table [ReportableResults] add constraint Unique_ReportableResults_ReportableResultCode_IsCalculated unique (R

eportableResultCode, IsCalculated);

But in the data there are multiple rows with the same values on these two columns?

As others have noted, it is very difficult to understand your question. I tried to work out a pattern from your sample data, but with the very long string values, it was difficult to get an overview.

You need to explain in more detaill what you are looking for. And as others have noted, it absolutely helps if you can provide CREATE TABLE statement for your tables and INSERT statements with sample data. This does not have to be the full database; you only need to distill data to focus on the actual problem. (And we would kind of appreciate if you could use shorter values than those in the screenshot.)
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2022-06-02T09:44:53.997+00:00

Also, you have added no less than five tags for your query. Four are related to SQL Server, whereas the fifth is for SQLite, which is a different product. What engine are you using? A query that works in SQL Server may not work on SQLite and vice versa.
Indudhar Gowda 426 Reputation points

2022-06-02T11:27:47.757+00:00

reportableresultid samplecode testprepetitioncode testcode parametercode reportableresultcode iscalculated createdonutc
uniqueidentifier nvarchar(200) nvarchar(50) nvarchar(50) nvarchar(50) nvarchar(200) bit datetimeoffset(7)
Indudhar Gowda 426 Reputation points

2022-06-02T11:30:40.743+00:00

Please check the ScreenShot
Bjoern Peters 8,921 Reputation points

2022-06-02T11:47:42.83+00:00

CREATE TABLE [dbo].[873855](
[reportableresultid] [uniqueidentifier] NULL,
[samplecode] nvarchar NULL,
[testprepetitioncode] nvarchar NULL,
[testcode] nvarchar NULL,
[parametercode] nvarchar NULL,
[reportableresultcode] nvarchar NULL,
[iscalculated] [bit] NULL,
[createdonutc] datetimeoffset NULL
) ON [PRIMARY]
GO

INSERT INTO [dbo].[873855] ([reportableresultid] ,[samplecode] ,[testprepetitioncode],[testcode],[parametercode],[reportableresultcode],[iscalculated],[createdonutc]) VALUES ('1328207E-434B-4124-8FA1-C99EDB3015B3', '202-2022-00044806', 'T-202-2022-0000080704', '2Z4PB', 'PAZ03G4', 'T-202-2022-0000080704PAZ03G41', 0,'2022-04-07 09:12:04.4783982 +00:00')
INSERT INTO [dbo].[873855] ([reportableresultid] ,[samplecode] ,[testprepetitioncode],[testcode],[parametercode],[reportableresultcode],[iscalculated],[createdonutc]) VALUES ('6E4CAF3F-FB37-4AF3-8734-D8C4AEA08205', '202-2021-00013862', 'T-202-2021-0000024673', '2Z031', 'PAZ0022', 'T-202-2021-0000024673PAZ00221', 0,'2021-03-08 04:40:53.9305471 +00:00')
INSERT INTO [dbo].[873855] ([reportableresultid] ,[samplecode] ,[testprepetitioncode],[testcode],[parametercode],[reportableresultcode],[iscalculated],[createdonutc]) VALUES ('FFC334BE-D009-4432-9257-4C1110F72E68', '202-2021-00001551', 'T-202-2021-0000002779', '2Z031', 'PAZ0022', 'T-202-2021-0000002779PAZ00223', 0,'2021-01-13 08:35:57.3148269 +00:00')

[...]
Bjoern Peters 8,921 Reputation points

2022-06-02T12:00:09.877+00:00

But your screenshot and your posts/replies doesn't really explain what you want to achieve.

Need SQL Query for [reportableresultcode] and [iscalculated] are unique constraint 2 columns, Take duplicates but dont take MAX Date.

After formatting and reading it 20 times...

Need SQL Query for [reportableresultcode] and [iscalculated]
Those are 2 columns with unique constraint
Take duplicates but dont take MAX Date. => Take all with maxdate

Please explain your target in detail, as my suggestion might not be correct

Accepted answer

0 additional answers

Your answer

Olaf Helper 47,436 Reputation points

2022-06-02T05:41:17.307+00:00

In the Above Screen Shot

We can't query screenshot to test it, so please post table design as DDL, some sample data as DML statement and the expected result.

Take duplicates but dont take MAX Date.

I don't understand a single word of that statement; may can you explain it more detailed, please.
Bert Zhou-msft 3,436 Reputation points

2022-06-02T05:52:45.517+00:00

Hi,@Indudhar Gowda

When you post the question, you should tell us more about the relationship between the three columns , how is the identification between the max and min dates ? Looking forward to more information from you.

Bert Zhou
Indudhar Gowda 426 Reputation points

2022-06-02T06:24:45.887+00:00

@Olaf Helper : Sorry Forgot to Upload Excel Sheet...

https://1drv.ms/x/s!Ag8QU6ar3yRugYxS_U4hYy43yNj_zg?e=DnH4Ws
Indudhar Gowda 426 Reputation points

2022-06-02T06:29:40.763+00:00

[reportableresultcode] and [iscalculated] are unique constraint 2 columns..

alter table [ReportableResults] add constraint Unique_ReportableResults_ReportableResultCode_IsCalculated unique (ReportableResultCode, IsCalculated);
Bert Zhou-msft 3,436 Reputation points

2022-06-02T06:34:44.237+00:00

Please post table design as DDL instead of excel sheet.
Indudhar Gowda 426 Reputation points

2022-06-02T06:50:03.393+00:00

Database is Huge and there are Multiple Columns have lot of Dependency....Just to look everything easy I have Given the dummy Data...
Bjoern Peters 8,921 Reputation points

2022-06-02T07:12:52.327+00:00

We would like to help you with your query, but we need to have at least to table definition of the source data table...

are those columns all nvarchar(max) or is there a uniqueidentifier or date column.... we don't really care about constraint... just the source DDL and ideally the data as INSERTs (not all rows but 10-20 rows)
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2022-06-02T08:48:07.33+00:00

[reportableresultcode] and [iscalculated] are unique constraint 2 columns..

>

alter table [ReportableResults] add constraint Unique_ReportableResults_ReportableResultCode_IsCalculated unique (R

eportableResultCode, IsCalculated);

But in the data there are multiple rows with the same values on these two columns?

As others have noted, it is very difficult to understand your question. I tried to work out a pattern from your sample data, but with the very long string values, it was difficult to get an overview.

You need to explain in more detaill what you are looking for. And as others have noted, it absolutely helps if you can provide CREATE TABLE statement for your tables and INSERT statements with sample data. This does not have to be the full database; you only need to distill data to focus on the actual problem. (And we would kind of appreciate if you could use shorter values than those in the screenshot.)
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2022-06-02T09:44:53.997+00:00

Also, you have added no less than five tags for your query. Four are related to SQL Server, whereas the fifth is for SQLite, which is a different product. What engine are you using? A query that works in SQL Server may not work on SQLite and vice versa.
Indudhar Gowda 426 Reputation points

2022-06-02T11:27:47.757+00:00

reportableresultid samplecode testprepetitioncode testcode parametercode reportableresultcode iscalculated createdonutc
uniqueidentifier nvarchar(200) nvarchar(50) nvarchar(50) nvarchar(50) nvarchar(200) bit datetimeoffset(7)
Indudhar Gowda 426 Reputation points

2022-06-02T11:30:40.743+00:00

Please check the ScreenShot
Bjoern Peters 8,921 Reputation points

2022-06-02T11:47:42.83+00:00

CREATE TABLE [dbo].[873855](
[reportableresultid] [uniqueidentifier] NULL,
[samplecode] nvarchar NULL,
[testprepetitioncode] nvarchar NULL,
[testcode] nvarchar NULL,
[parametercode] nvarchar NULL,
[reportableresultcode] nvarchar NULL,
[iscalculated] [bit] NULL,
[createdonutc] datetimeoffset NULL
) ON [PRIMARY]
GO

INSERT INTO [dbo].[873855] ([reportableresultid] ,[samplecode] ,[testprepetitioncode],[testcode],[parametercode],[reportableresultcode],[iscalculated],[createdonutc]) VALUES ('1328207E-434B-4124-8FA1-C99EDB3015B3', '202-2022-00044806', 'T-202-2022-0000080704', '2Z4PB', 'PAZ03G4', 'T-202-2022-0000080704PAZ03G41', 0,'2022-04-07 09:12:04.4783982 +00:00')
INSERT INTO [dbo].[873855] ([reportableresultid] ,[samplecode] ,[testprepetitioncode],[testcode],[parametercode],[reportableresultcode],[iscalculated],[createdonutc]) VALUES ('6E4CAF3F-FB37-4AF3-8734-D8C4AEA08205', '202-2021-00013862', 'T-202-2021-0000024673', '2Z031', 'PAZ0022', 'T-202-2021-0000024673PAZ00221', 0,'2021-03-08 04:40:53.9305471 +00:00')
INSERT INTO [dbo].[873855] ([reportableresultid] ,[samplecode] ,[testprepetitioncode],[testcode],[parametercode],[reportableresultcode],[iscalculated],[createdonutc]) VALUES ('FFC334BE-D009-4432-9257-4C1110F72E68', '202-2021-00001551', 'T-202-2021-0000002779', '2Z031', 'PAZ0022', 'T-202-2021-0000002779PAZ00223', 0,'2021-01-13 08:35:57.3148269 +00:00')

[...]
Bjoern Peters 8,921 Reputation points

2022-06-02T12:00:09.877+00:00

But your screenshot and your posts/replies doesn't really explain what you want to achieve.

Need SQL Query for [reportableresultcode] and [iscalculated] are unique constraint 2 columns, Take duplicates but dont take MAX Date.

After formatting and reading it 20 times...

Need SQL Query for [reportableresultcode] and [iscalculated]
Those are 2 columns with unique constraint
Take duplicates but dont take MAX Date. => Take all with maxdate

Please explain your target in detail, as my suggestion might not be correct

Answer 1

Jingyang Li 5,896 Volunteer Moderator

SELECT 
[reportableresultid]
      ,[samplecode]
      ,[testprepetitioncode]
      ,[testcode]
      ,[parametercode]
      ,[reportableresultcode]
      ,[iscalculated]
      ,[createdonutc]
FROM (SELECT   [reportableresultid]
      ,[samplecode]
      ,[testprepetitioncode]
      ,[testcode]
      ,[parametercode]
      ,[reportableresultcode]
      ,[iscalculated]
      ,[createdonutc]
      ,row_number() Over(partition by reportableresultcode,  iscalculated  order by [createdonutc] desc) rn
  FROM  [dbo].[yourtablename]
  ) t
  where rn>1

Indudhar Gowda 426 Reputation points

2022-06-02T18:44:07.54+00:00

Exactly what I was looking for.....Thanks @Jingyang Li

Share via

Need SQL Query for unique constraint 2 columns take all Min date except Max Date

0 additional answers

Your answer