Selecting n rows from similar  rows ms sql 2005

Posted on 2007-10-01
Last Modified: 2010-03-20
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

Question by:robrodp
    LVL 17

    Expert Comment


    Is this what you are looking for?
    SELECT DISTINCT NAME, Dept from table

    Author Comment

    No, I want 5 names for each department. Do not care which
    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    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


    Aneesh R
    LVL 68

    Accepted Solution

    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.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now