April 18, 2014 at 1:23 am
Hi,
Please help me to write a query which retrieves only unique rows excluding some columns.
IdStatusmanager Team Comments Proj number Date
19391New XUnassigned One 3732.0 16-Apr-14
19392Can YCustomer Two 3732.0 17-Apr-14
19393Can YCustomer Two 3732.0 17-Apr-14
19394Can YCustomer One 3732.0 18-Apr-14
19395New YCustomer One 3732.0 19-Apr-14
19396New YCustomer One 3732.0 21-Apr-14
19397New ZCustomer One 3732.0 20-Apr-14
In the above table project number and id shouldn't be considered and I should get the unique rows considering rest of columns and sorted based on date. Expected result is
IdStatusmanager Team Comments Proj number Date
19391New XUnassigned One 3732.0 16-Apr-14
19392Can YCustomer Two 3732.0 17-Apr-14
19394Can YCustomer One 3732.0 18-Apr-14
19395New YCustomer One 3732.0 19-Apr-14
19397New ZCustomer One 3732.0 20-Apr-14
19396New YCustomer One 3732.0 21-Apr-14
Please help. Thanks in advance. May be simple one or may be already answered, but i could not locate what i am expecting. Sorry if it is a duplicate question
April 18, 2014 at 1:36 am
Hey Prasanna,
It would be also useful to provide table DDL and sample DML to get your answer quickly.If you don't mind, please post it.
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
April 18, 2014 at 1:45 am
WITH cte AS
(
SELECT
Id,[Status],manager,Team,Comments,[Proj number],[Date],
ROW_NUMBER() OVER(PARTITION BY [Status],manager,Team,Comments,[Date] ORDER BY Id ASC) rn
FROM YourTable
)
SELECT Id,[Status],manager,Team,Comments,[Proj number],[Date]
FROM cte
WHERE rn = 1
ORDER BY [Date] ASC
___________________________
Do Not Optimize for Exceptions!
April 18, 2014 at 3:16 am
Thank you very much Milas. Its working good, i am still testing with still more values. 🙂
April 18, 2014 at 3:42 am
Bals.Prasanna (4/18/2014)
Thank you very much Milas. Its working good, i am still testing with still more values. 🙂
It is nice you got your solution.Actually it is very generic query to remove duplicates in any table and I deliberately did not put it earlier because we want you to follow the rules in this forum which infact help you to get your reply in a quick time.hope you don't mind. 🙂
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply