robrodp
asked on
Selecting n rows from similar rows ms sql 2005
I have a table with 2 fields name and dept.
Say 2000 records an 5 departments.
I need to select 5 names of each department. (No matter which)
I f I needed 1 it woul be very simple
select max(name),dept from table group by dept
But I need 5 not only 1
Say 2000 records an 5 departments.
I need to select 5 names of each department. (No matter which)
I f I needed 1 it woul be very simple
select max(name),dept from table group by dept
But I need 5 not only 1
ASKER
No, I want 5 names for each department. Do not care which
Hello robrodp,
DECLARE @sql VARCHAR(8000)
SELECT @SQL = COALESCE(@sql+' union all ', '') +'SELECT TOP 5 name, deptID from urTble WHERE deptid = '''+ deptID +''''+char(13)
FROM urTble
EXEC(@sql)
Aneesh R
DECLARE @sql VARCHAR(8000)
SELECT @SQL = COALESCE(@sql+' union all ', '') +'SELECT TOP 5 name, deptID from urTble WHERE deptid = '''+ deptID +''''+char(13)
FROM urTble
EXEC(@sql)
Aneesh R
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Is this what you are looking for?
SELECT DISTINCT NAME, Dept from table