question

IDGO-6443 avatar image
0 Votes"
IDGO-6443 asked IDGO-6443 commented

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

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

207720-image.png

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 ..


sql-server-generalsql-server-transact-sqlazure-sql-databasesql-server-reporting-servicesdotnet-sqlite
image.png (92.1 KiB)
· 13
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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.
1 Vote 1 ·

Hi,@IDGO-6443
207781-image.png
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


1 Vote 1 ·
image.png (6.4 KiB)
IDGO-6443 avatar image IDGO-6443 BertZhoumsft-7490 ·

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

alter table [ReportableResults] add constraint Unique_ReportableResults_ReportableResultCode_IsCalculated unique (ReportableResultCode, IsCalculated);


0 Votes 0 ·

[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.)
0 Votes 0 ·

Please post table design as DDL instead of excel sheet.

0 Votes 0 ·
IDGO-6443 avatar image IDGO-6443 BertZhoumsft-7490 ·

Database is Huge and there are Multiple Columns have lot of Dependency....Just to look everything easy I have Given the dummy Data...

0 Votes 0 ·
Show more comments

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.

0 Votes 0 ·

1 Answer

JingyangLi avatar image
1 Vote"
JingyangLi answered IDGO-6443 commented
 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
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Exactly what I was looking for.....Thanks @JingyangLi

0 Votes 0 ·