SQLTeam.com | Weblogs | Forums

How to Prevent Duplicate values

How to Prevent Duplicate values

image

please help

First things first - these are not duplicates. Duplicates occur when all columns have the exact same value, but in your case the added date/time column is different for every row of the so-called duplicates.

Assuming you want to eliminate all but the latest row for each email address you can use a group by, grouping by EmailAddress, Address and Full_Name - returning MAX(AddedDateTime).

If that doesn't meet the requirements, then please provide sample data and expected results. Sample data should be provided using create and insert statements so we can generate the sample data on our system to test and validate a solution.

1 Like

Please defined duplicate in these rows as some have different AddedDateTime or there could be more columns not shown.
from the rows you highlighted which one do you want based on AddedDateTime?

1 Like

Dear Friends I need to out put as highlighted top record

Why that specific record. What about it is unique from the rest.

Highlighting on yellow is not enough of a requirement

base on AddedDateTime

Need to capture the last logging date only for the Specified user please advise friend

row number ( partition by emailaddress, address, Full_name order by addeddate desc )

where row number = 1

1 Like

You mean the 1st AddedDateTime

If you don't need any row other than the first one (chronologically), then add a trigger to the table so that dup rows are never inserted in the first place.

Yep I need show Most Last AddedDateTime Record to show please help brothers

In your sample you have both DISTINCT and GROUP BY. In your sample you do not have any aggregate functions. Remove the DISTINCT as it isn't needed and add a MAX(AddedDateTime) and remove AddedDateTime from the group by.

If there are other columns you need to include and can't use an aggregate or include them in the grouping then you can use row_number() function.

image
please explain why this error happen

please kindly mention how to add that field too

ambiguos means the column is there in more than one table you are using in your query
you have specify the table name

example

select 
   age 
from 
   name 
     join 
   address 
      on name.id= address.id

if it says ambiguous means age is there in both tables name and address

you have to specify

select 
   name.age 
from 
   name 
     join 
   address 
      on name.id= address.id

Superb Help Thanks