Share via

Productstatus order by Productnumber

Shambhu Rai 1,411 Reputation points
2022-05-24T02:55:30.147+00:00

Hi Expert,

I wanted to calculate produnumber, min (PrdStartDate), Prodstatus and Prodstatus must be unique and not duplicate and order by prodnumber

Create table
CREATE TABLE [dbo].testdata ON [PRIMARY]
GO

insert into [dbo].[testdata]

values('Prod1000','873','7','2021-06-16', '11-05-2022', '4-Westcoast'),
('Prod1000','873', '18', '2022-05-12',NULL,'4-Westcoast'),
('Prod1000', '873', '19', '2022-05-12', NULL,'5-Eastcoast'),
('Prod1000', '1254','4', '2022-03-28', '2022-03-30', '4-Westcoast'),
('Prod10000', '1254','1', '2022-03-3', NULL, '7-Racecourse'),
('Prod10000', '4427', '5', '2022-03-28', '2022-03-31', '7-Racecourse'),
('Prod10000', '4427', '8', '2022-04-01', NULL, '8-Tenniscourt'),
('Prod10000', '4427', '8', '2022-04-01', NULL, '8-Tenniscourt')

expected output

('Prod1000','873','7','2021-06-16', '11-05-2022', '4-Westcoast')

('Prod1000', '873', '19', '2022-05-12', NULL,'5-Eastcoast')

('Prod10000', '1254','1', '2022-03-3', NULL, '7-Racecourse')

('Prod10000', '4427', '8', '2022-04-01', NULL, '8-Tenniscourt'),

SQL Server Reporting Services
SQL Server Reporting Services

A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.

SQL Server Integration Services
Developer technologies | Transact-SQL
Developer technologies | Transact-SQL

A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.

SQL Server | Other
SQL Server | Other

Additional SQL Server features and topics not covered by specific categories

0 comments

Answer accepted by question author

Tom Cooper 8,501 Reputation points
2022-05-24T03:26:49.75+00:00 
;With cte As
(Select Prodnumber, Prodid, Prodstatusid, PrdStartDate, PrdEndDate, prodstatus,
  Row_Number() Over(Partition By Prodnumber, prodstatus Order By PrdStartDate) As rn
From dbo.testdata)
Select Prodnumber, Prodid, Prodstatusid, PrdStartDate, PrdEndDate, prodstatus
From cte
Where rn = 1
Order By Prodnumber;

Tom

Was this answer helpful?

1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Bert Zhou-msft 3,521 Reputation points
    2022-05-24T07:14:32.54+00:00

    Hi，@Shambhu Rai

    Welcome to Microsoft T-SQL Q&A Forum!

    I agree with TOM's answer . There are many ways to delete duplicate values . Through the RANK function, ROW_NUMBER function, max function, etc., the idea of ​​implementing is the same：

    Partition the columns with the same value to find these expected results . Please refer to this link .

    Bert Zhou

    Was this answer helpful?

    2 people found this answer helpful.
    0 comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.