getting one record - 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

Transact-SQL (2000)

getting one record

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-09-10 : 23:23:06

I have a table with emp_ids and emp_name and task_id.Here the emp_id is repeting Now I have to get only one record out of each section.

eg:-
emp_id emp_name task_id
1 raj 232
1 john 566
2 johny 898
3 lee 787
3 kaer 788

here i want to group and get only one record for a emp_id

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-11 : 00:07:21

What should this one record look like??

Select
emp_Id,
emp_name,
???
From myTable
Group By emp_Id, emp_name

Corey

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-09-11 : 00:21:21

simply saying i want only one emp_id and its details out of the each group of the emp_ids.it doesn't matter which one min or max..

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-09-11 : 07:37:43

for the above input data...show us some expected results....and we might be able to read your mind.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-11 : 11:24:42

How does this grab you?


Declare @myTable Table (empId int, empName varchar(100), taskId int)
Insert Into @myTable
Select 1, 'raj', 232
Union All Select 1, 'john', 566
Union All Select 2, 'johny', 898
Union All Select 3, 'lee', 787
Union All Select 3, 'kaer', 788


Select 
	* 
From @myTable A
Where 0 = (Select count(*) From @myTable Where empId = A.empId and empName < A.empName)

Corey

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-11 : 13:17:34

Corey,
do you know about any performance difference between method 1, 2 below ?

create Table #myTable(empId int, empName varchar(100), taskId int)
Insert Into #myTable
Select 1, 'raj', 232
Union All Select 1, 'john', 566
Union All Select 2, 'johny', 898
Union All Select 3, 'lee', 787
Union All Select 3, 'kaer', 788

-- 1
Select 
	a.empId,
	a.empName,
	a.taskId 
From
	#myTable a
Where
	0 = ( Select count(*) From #myTable Where empId = a.empId and empName < a.empName )

-- 2
select
	a.empId,
	a.empName,
	a.taskId
from
	#myTable a
	join ( select empId, min(empName) as empName from #myTable group by empId ) grp
	on a.empId = grp.empId and a.empName = grp.empName

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-11 : 15:01:41

Well the execution plan for #2 was cheaper, but I didn't think of it...

sqllearner: go with plan #2 if our mindreading is correct!

Corey

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-09-13 : 15:33:36

Here with the select the group by I need is with the emp_name and then it gives this error :
Column 'emp_id is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources