• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 331
  • Last Modified:

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

0
robrodp
Asked:
robrodp
1 Solution
 
Daniel ReynoldsSoftware Applications Developer / IntegratorCommented:

Is this what you are looking for?
SELECT DISTINCT NAME, Dept from table
0
 
robrodpAuthor Commented:
No, I want 5 names for each department. Do not care which
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
0
 
Scott PletcherSenior DBACommented:
SELECT dept, name
FROM (
    SELECT dept, name, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY NEWID()) AS rowNum
    FROM tableName    
) AS derived
WHERE rowNum <= 5
ORDER BY dept, name


NEWID() will give you a random sampling of names; you could of course ORDER BY name to get the first 5 names.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now