Query help - 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 2000 Forums

SQL Server Development (2000)

Query help

Author

Topic

akwok
Starting Member

10 Posts

Posted - 2006-08-29 : 14:40:03

Should be very easy for most of you

I have a table like the following

empId name salary
01 Mike 50
01 Mike 56
02 Nancy 30
03 June 30

at first glance, of course, the data is errorneous, however that's what most companies have in their db.

I want to write a query that will ignore all the records that has the same ID except the first retrieved one, so it should return

01 Mike 50
02 Nancy 30
03 June 30

Thanks for help.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-29 : 14:47:25

Tables have no inherent order, so what would be the basis for deciding what the first one is?

CODO ERGO SUM

akwok
Starting Member

10 Posts

Posted - 2006-08-29 : 14:53:58

thanks for quick replies

It really doesn't matter, the first returned one will be selected. that's all i need to do.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-29 : 15:09:33

You are missing the point. To do what you want, you have to be able to identify something in the data that means a row is "first".

I don't suppost this table has a primary key? If so what is it?

CODO ERGO SUM

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-29 : 15:15:35

If you don't care which one to return, then you can use this:

SELECT empId, name, MIN(salary) AS salary
FROM YourTable
GROUP BY empId, name

Tara Kizer

akwok
Starting Member

10 Posts

Posted - 2006-08-29 : 15:20:48

thanks, if my database is very crappy. the value of age column, and name column can be different for 2 records of the same empID
should it be

SELECT empID, min(name) as name, min(salary) as salary, min(age) as age
from yourtable
group by EmpID

?
I hope i am a real dba. thx.

akwok
Starting Member

10 Posts

Posted - 2006-08-29 : 15:23:47

to Michael,
I am a human ressources admin, i got assigned to work with this database by surprise. all they want me to do is to generate a table with unique empID. They don't care what values the other column is selected. the principal idea is really have a table with unique empID, because after all, it's testing data so our developpers can work on it.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-29 : 15:28:55

quote:
Originally posted by akwok

thanks, if my database is very crappy. the value of age column, and name column can be different for 2 records of the same empID
should it be

SELECT empID, min(name) as name, min(salary) as salary, min(age) as age
from yourtable
group by EmpID

?
I hope i am a real dba. thx.

I don't understand what you are asking. Please show us some data.

Tara Kizer

akwok
Starting Member

10 Posts

Posted - 2006-08-29 : 15:31:51

data could be like this

empID name salary age
1 mike 50 20
1 mike 40 25
1 nancy 30 25
2 june 30 30
3 daniel 20 20

notice that all columns, like name salary and age can contain different values for records having the same empID.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-29 : 15:34:56

You need a derived table now.


SELECT t1.empId, t1.[name], t1.salary, t1.age
FROM YourTable t1
INNER JOIN
(
	SELECT empId, MIN(salary) AS salary
	FROM YourTable
	GROUP BY empId
) t2
ON t1.empId = t2.empId AND t1.salary = t2.salary

Tara Kizer

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-29 : 15:51:14

You can give each row a Unique ID in a temp table


select
	-- Create a unique ID for each row
	NewID =identity(int,1,1),
	empID,
	name,
	salary,
	age
into
	#temp
from
	MyTable
order by
	empID,
	name,
	salary,
	age

select
	empID,
	name,
	salary,
	age
from
	#temp
where
	NewID in
	(
	select
		NewID = min(NewID)
	from
		#temp
	group by
		empID,
		name,
		salary,
		age
	)

drop table 	#temp

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources