How To Avoid The Duplicate Records....

Question

How To Avoid The Duplicate Records....

sarwaanmca

Right there with Babe

Points: 746
More actions
April 29, 2013 at 11:39 am

#394689

Hi Friends...
I Having Table For The Following Structure..
create Table EmployeeDet (EmpID int,EmpName Varchar(100),Department varchar(100),Age int,salary int)
insert into EmployeeDet values (1,'Arun','Accounts',25,25000),(1,'Arun','Accounts',23,21000),(1,'Arun','Accounts',26,22000),(1,'Arun','Accounts',29,27000)
insert into EmployeeDet values (2,'kumar','Production',23,18000),(2,'kumar','Production',27,30000),(2,'kumar','Production',31,19000)
insert into EmployeeDet values (3,'saran','Sales',22,38000)
insert into EmployeeDet values (4,'Vidya','Purchase',18,20000),(4,'Vidya','Purchase',27,36000),(4,'Vidya','Purchase',23,35000)
select * from EmployeeDet
EmpIDEmpNameDepartmentAgesalary
1ArunAccounts2525000
1ArunAccounts2321000
1ArunAccounts2622000
1ArunAccounts2927000
2kumar Production2318000
2kumar Production2730000
2kumar Production3119000
3saranSales 2238000
4VidyaPurchase1820000
4VidyaPurchase2736000
4VidyaPurchase2335000
Below I have mentioned The my Required Table Data
EmpIDEmpNameDepartmentAgesalary
1ArunAccounts2525000
2kumarProduction2318000
3saranSales 2238000
4VidyaPurchase1820000
My Requirement :
1) EmpID,EmpName,Department Field Should not be Duplicate Record...
2) You Take First Record or Last Record or Middle Record of the Particular EmpID
but EmpID,EmpName,Department Should be Unique...
( Here I Mentioned First Record of The EmpID )...
Thanks & Regards,
Saravanan.D

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

sarwaanmca (4/29/2013)
Hi Friends...
I Having Table For The Following Structure..
create Table EmployeeDet (EmpID int,EmpName Varchar(100),Department varchar(100),Age int,salary int)
insert into EmployeeDet values (1,'Arun','Accounts',25,25000),(1,'Arun','Accounts',23,21000),(1,'Arun','Accounts',26,22000),(1,'Arun','Accounts',29,27000)
insert into EmployeeDet values (2,'kumar','Production',23,18000),(2,'Arun','Production',27,30000),(2,'Arun','Production',31,19000)
insert into EmployeeDet values (3,'saran','Sales',22,38000)
insert into EmployeeDet values (4,'Vidya','Purchase',18,20000),(4,'Vidya','Purchase',27,36000),(4,'Vidya','Purchase',23,35000)
select * from EmployeeDet
EmpIDEmpNameDepartmentAgesalary
1ArunAccounts2525000
1ArunAccounts2321000
1ArunAccounts2622000
1ArunAccounts2927000
2kumarProduction2318000
2ArunProduction2730000
2ArunProduction3119000
3saranSales 2238000
4VidyaPurchase1820000
4VidyaPurchase2736000
4VidyaPurchase2335000
Below I have mentioned The my Required Table Data
EmpIDEmpNameDepartmentAgesalary
1ArunAccounts2525000
2kumarProduction2318000
3saranSales 2238000
4VidyaPurchase1820000
My Requirement :
1) EmpID,EmpName,Department Field Should not be Duplicate Record...
2) You Take First Record or Last Record or Middle Record of the Particular EmpID
but EmpID,EmpName,Department Should be Unique...
( Here I Mentioned First Record of The EmpID )...
Thanks & Regards,
Saravanan.D

how do we determine record order...to find first/last/middle

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

sarwaanmca Right there with Babe Points: 746 More actions · Answer 2

what Ever You Take its ur wish...

thats not a problem...

you take it first record of the EmpID....

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 3

sarwaanmca (4/29/2013)
what Ever You Take its ur wish...
thats not a problem...
you take it first record of the EmpID....

ok...so what do you say is the first record for Arun?

ordered by salary or by age?

its ur db not mine

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

sarwaanmca Right there with Babe Points: 746 More actions · Answer 4

sarwaanmca

Right there with Babe

Points: 746

April 29, 2013 at 12:21 pm

#1610944

you pls show the First Record of the EmpID

Lowell SSC Guru Points: 323506 More actions · Answer 5

This looks an awful lot like homework, so I'll frame my answer with that in mind.

It looks like the purpose of this exercise is to learn how to use the GROUP BY statement.

The group by will return one unique record for the columns defined by the GROUP BY;

I think you've already determined what you think you want to avoid duplicates of, so use that for your GROUP BY definition.

Read up on how to use GROUP BY , create a query that features it and the columns you are trying to uniquely identify;

let us know how that works for you.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1003875 More actions · Answer 6

This is a test for normalization and, I agree. It looks a lot like homework.

First, you should never ever store age in a database. It can go out of date the second you enter it. Always use a DOB instead. If you need age, create a calclulated column. Note that this cannot be a persisted calculated column because it needs to change when you look at it, not when you update the DOB which should never happen.

Never store other information other than EmployeeID with Salary. It should be in a second table as a Type II Slowly Changing Dimension. Since this is likely homework, I'm not going to tell you how to work that. Please Google it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sarwaanmca Right there with Babe Points: 746 More actions · Answer 7

hi Friends..

i got the Result ...

Select EmpID,EmpName,Department,max(Age),MAX(salary) from

EmployeeDet

group by EmpID,EmpName,Department

Thanks for Your valuable Guidance

Regards,

Saravanan.D

Lowell SSC Guru Points: 323506 More actions · Answer 8

excellent! glad we could guide you in the right direction; I for one am very glad you were able to solve this with just a gentle nudge!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sarwaanmca Right there with Babe Points: 746 More actions · Answer 9

but stili i having a doubt ..

how to show the fist Record of the EmpID

Thanks & Regards,

Saravanan.D

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 10

sarwaanmca (4/29/2013)
hi Friends..
i got the Result ...
Select EmpID,EmpName,Department,max(Age),MAX(salary) from
EmployeeDet
group by EmpID,EmpName,Department
Thanks for Your valuable Guidance
Regards,
Saravanan.D

consider this

create Table EmployeeDet (EmpID int,EmpName Varchar(100),Department varchar(100),Age int,salary int)

insert into EmployeeDet values (1,'Arun','Accounts',25,25000),(1,'Arun','Accounts',23,21000),(1,'Arun','Accounts',26,22000),(1,'Arun','Accounts',29,27000), (1,'Arun','Accounts',35,20000)

insert into EmployeeDet values (2,'kumar','Production',23,18000),(2,'kumar','Production',27,30000),(2,'kumar','Production',31,19000)

insert into EmployeeDet values (3,'saran','Sales',22,38000)

insert into EmployeeDet values (4,'Vidya','Purchase',18,20000),(4,'Vidya','Purchase',27,36000),(4,'Vidya','Purchase',23,35000)

select * from EmployeeDet

Select EmpID,EmpName,Department,max(Age),MAX(salary) from

EmployeeDet

group by EmpID,EmpName,Department

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

sarwaanmca (4/29/2013)
but stili i having a doubt ..
how to show the fist Record of the EmpID
Thanks & Regards,
Saravanan.D

One of the things you need to understand, there is no concept of first or last in SQL Server table in and of itself. You have to define what you mean by first and last and then you need a means to identify it. For instance, first record entered. How, based solely on the data would you identify that? With your sample data and table structure, you can't. What change(s) do you think you would have to make to know which record was inserted first?

Jeff Moden SSC Guru Points: 1003875 More actions · Answer 12

sarwaanmca (4/29/2013)
hi Friends..
i got the Result ...
Select EmpID,EmpName,Department,max(Age),MAX(salary) from
EmployeeDet
group by EmpID,EmpName,Department
Thanks for Your valuable Guidance
Regards,
Saravanan.D

That will give you single rows but it will not give you the latest value for Salary. It will only give you the value for max salary.The correct value for the latest salary would be the one with the oldest age for each employee. The ROW_NUMBER() OVER method would likely be the best bet for this.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)