Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2010
    Posts
    3

    Unanswered: Get Top(n) records from each department

    Hi,

    i have employee table data like follow

    id name dept_id

    1 sudhakar 11
    2 raju 12
    3 kalidindi 13
    4 rajskr 11
    5 samsuns 11
    6 pavan 12
    7 rajesh 12
    8 poul 12
    9 jhon 13
    10 malgudi 13

    i want only top 2 records from each department.

    Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    top 2 based on what?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by r937 View Post
    top 2 based on what?
    I had the same thought, but as is the order by desc gives the top 2 in conjuction with select top 2. There is no criteria for selecting here.

  4. #4
    Join Date
    Feb 2007
    Location
    Chennai, TN
    Posts
    68

    Get Top(n) records from each department

    Hi,
    I dont think so, it can be done using a single query.

    You need to use a stored procedure.

    use a cursor for getting each department id. then use top 2 and fetch the top 2 for each department and put it in the temp table.

    Do the above steps till all department ids are done with.

    at the end select the result set from the temp table.

    Thanks,
    Dinesh

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by parangiri View Post
    I dont think so, it can be done using a single query.
    yes it can -- there is no need to resort to temp tables (a most frightening idea)

    Code:
    SELECT id
         , name
         , dept_id
      FROM employees AS t
     WHERE ( SELECT COUNT(*) 
               FROM employees
              WHERE dept_id = t.dept_id
                AND name > t.name ) < 2
    see? top 2 names in each department, using a single query

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yeah - the OP needs to define TOP 2. Personally, I prefer using the windows aggregate functions.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Code:
    select * 
    from 
    (select *, rowid=row_number() over (partition by dept_id order by dept_id) 
     from (select 
     /**** Test data start ****
     id, name,        dept_id              */ 
      1, 'sudhakar',  11 union all select
      2, 'raju',      12 union all select
      3, 'kalidindi', 13 union all select
      4, 'rajskr',    11 union all select
      5, 'samsuns',   11 union all select
      6, 'pavan',     12 union all select
      7, 'rajesh',    12 union all select
      8, 'poul',      12 union all select
      9, 'jhon',      13 union all select
     10, 'malgudi',   13)employee(
     id, name,        dept_id)
     /**** Test data end ****/
    )view1
    where rowid<=2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •