Removing Duplicates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Removing Duplicates

Author

Topic

ishchopra
Starting Member

24 Posts

Posted - 2011-11-02 : 06:02:05

Hello Experts,

I am trying to solve one problem but my output is not correct, please help:

I have one table in which there are about 1,00,000 records, i need to find the correct values and unique records. i am explaining the dependency below:-

Table Name "Ongoing_Data"

Columns and Data

TENDERID........CONTRACTID..........DATE_PRICED......FINAL_STATUSABC00123......... CON0124............02/06/2011......Approved
ABC00123......... CON0125............03/06/2011......Accepted
ABC00124......... CON0126............12/06/2011......LOST
ABC00124......... CON0127............13/06/2011......LOST
ABC00124......... CON0128............14/06/2011......LOST
ABC00128......... CON0221............12/08/2011......Approved
ABC00128......... CON0222............13/08/2011......LOST
ABC00130......... CON0352............06/09/2011......Approved
ABC00130......... CON0353............07/09/2011......Accepted

As per the business rule Tender_ID can have multiple contractid's.. now i want to have unique records based on Tender id with two rules

1. When the Final_Status is accepted then it should only consider accpeted record.

2. if there is no accepted status in the Final_Status then it should pick up the latest date_priced record.

i have written below query but it is not correct.

SELECT *

FROM ongoing_data A

where A.date_priced =

(
select max(date_priced)
from ongoing_data B
where
A.Tenderid = B.Tenderid
)

or A.final_status = "Accepted"
order by 1,2

Please help..........

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-02 : 06:15:22

[code]
SELECT TENDERID,CONTRACTID,DATE_PRICED,FINAL_STATUS
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY TENDERID ORDER BY CASE WHEN FINAL_STATUS='Accepted' THEN 0 ELSE 1 END,DATE_PRICED DESC) AS Rn,*
FROM ongoing_data
)t
WHERE rn=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ishchopra
Starting Member

24 Posts

Posted - 2011-11-02 : 06:42:23

Hello Visakh,

Brilliant, i guess this will work on perfectly, unfortunelty i m using Access 2007 and doing all my query on SQL view..

CASE doesnt work in Access..

Can you make some changes please ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-02 : 07:26:25

quote:
Originally posted by ishchopra

Hello Visakh,

Brilliant, i guess this will work on perfectly, unfortunelty i m using Access 2007 and doing all my query on SQL view..

CASE doesnt work in Access..

Can you make some changes please ?

I dont know Access. so not sure what you should be using. perhaps IIF is what you should be using

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rischfre
Starting Member

15 Posts

Posted - 2011-11-04 : 10:46:21

Hi

There is a wizard in Access in order to identified duplicates. This is an option when you create a new query.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources