Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
i have a search page if i want to search it will give the content search field records here send from ,send to dates are there when i enter dates n search it will give duplicate records here one i sent notification to two companies with one id so it will give when i entered dates two records according to how many i sent notifications to companies if i sent to three companies it will show in search page 3 times but i want one time when enter dates send from and send to dates...please give to me solution...
Posted 13-Feb-13 23:40pm
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

from sql bible there is a solution I will copy & paste it for you
 
Deleting duplicate rows using windowing
 
Of the three methods to remove duplicate rows, this method is the most straightforward because it
doesn't need to alter the table or generate a second table.
The key to this method is using the windowing’sOVER()clause with aROW_NUMBER()function and
a partition. The partition will begin renumbering with every new partition. Set theOVER()clause to
PARTITION BYevery column to be checked for duplicate data. In this case, every column is being
checked.
Running the windowing query first shows how it applies the row number:
 

 
SELECT Col1, Col2,
ROW_NUMBER() OVER (PARTITION BY Col1, Col2ORDER BY Col1) AS rn
FROM DupsNoPK
 
 

Result:
Col1 Col2 rn
----------- ----- --------------------
1 abc 1
2 abc 1
2 abc 2
2 abc 3
7 xyz 1
7 xyz 2
Every duplicate row has anrnvalue of greater than 1, so it’s now easy to delete the duplicates:
 
WITH DupsNumbered
AS (
SELECT Col1, Col2,
ROW_NUMBER() OVER (PARTITION BY Col1, Col2 ORDER BY Col1) AS rn
FROM DupsNoPK
)
DELETE DupsNumbered
WHERE rn > 1;
 

The nextSELECTtests the effect of the windowing remove duplicates query:
 
SELECT Col1, Col2
FROM DupsNoPK;
 

Result:
Col1 Col2
----------- -----
1 abc
2 abc
7 xyz
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

I think in this question you ask arround 2 questions
 
1) Avoid duplicate records while calling the Select Query itself . In your select query itself you avoid to show that duplicate values using DISTINCT, GROUP BY...
 
2)your asking something from & to dates are you using "fromdate between todate" in your SQL query itself.
 
If you want more clariffication about this search in google "Distinct , Group By clause, between in SQL".
 
Then you get one idea about this..
  Permalink  
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

simplest trick is,
 
1.
select distinct * from tablename
 
2.
now copy result data,
and put in excel sheet
 
3.
delete all data from your table using
Delete from tablename
now right click table and open it
copy filtered data you have put in excel and paste in table
 
Happy Coding!
Smile | :)
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 406
1 /\jmot 180
2 Suraj Sahoo | Coding Passion 170
3 BillWoodruff 159
4 Afzaal Ahmad Zeeshan 154
0 OriginalGriff 8,344
1 Sergey Alexandrovich Kryukov 7,407
2 DamithSL 5,634
3 Maciej Los 5,024
4 Manas Bhardwaj 4,986


Advertise | Privacy | Mobile
Web01 | 2.8.1411023.1 | Last Updated 14 Feb 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100