How do you select 1 record per group from a table

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)

How do you select 1 record per group from a table

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-09-11 : 14:12:54

Hector Corella writes "I have a table with training records for a list of persons.
I need to figure out who needs more training. "If someone has just 1 training course he's going to needed more than somebody with 2 or more training sessions"

The records consist of:
EmployeeNo, TrainingCourse, Shift, ProductionLine

The trick here is that I can only pick one from each shift and each production line.
I solved the problem by doing this -shown below, but it's too slow and it still need some filtering to get it right.

vwEntrenamientosxPadron is just a join between the training records table and the Employee table.

SELECT CASE WHEN (SELECT Count(*)
FROM vwEntrenamientosxPadron a2
WHERE a2.Prodline = a1.Prodline and
a2.Shift = a1.Shift AND
a2.EmpNo <= a1.EmpNo)=1
THEN a1.EmpNo
ELSE 0
END AS "Train",a1.EmpNo,a1.prodLine,a1.shift
FROM vwEntrenamientosxPadron a1

Thanks
HCorella
*** Using SQL 2000 with Srv.Pack 2 running under Win 2K Server/Wkst"

nr
SQLTeam MVY

12543 Posts

Posted - 2002-09-12 : 03:56:07

select Train = case when count(*) = 1 then EmpNo else 0 end
from vwEntrenamientosxPadron
group by EmpNo

oops - misread the query.
something like

select EmpNo
from vwEntrenamientosxPadron a1
where EmpNo =
(select top 1 a2.EmpNo from vwEntrenamientosxPadron a2
where a2.Prodline = a1.Prodline
and a2.Shift = a1.Shift group by a2.eEmpNo order by count(*)
)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Edited by - nr on 09/12/2002 03:59:48

Edited by - nr on 09/12/2002 04:05:10

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources