How to select the top 2 of each id Group in SQL server

I have the the following query and against each id im getting more then 3 action/code Can you adivse me how to get the top two actions against each id by using a select statement instead of writing a procedure

SELECT     id, 
      开发者_StackOverflow中文版     code,
           date,
           des
FROM         accounts INNER JOIN
                      accdesc ON accoints.code = accdesc.act_cd
ORDER BY accounts.id

query is returning the following data

id  code    date        desc 

609 AG1     2005-11-07  a                    
609 AG2     2004-11-12  a clear                 
609 AG2     2002-04-28  a clear                 
609 AG2     1998-07-20  a clear                 
609 AG2     1998-03-16  a clear                 
627 AG9     2010-02-24  a9
627 AONOSP  2010-02-19  possession  
627 AONSPV  2010-02-24  visit            
627 SP AG1  2007-11-01  agreement                  
627 AONSPV  2010-02-22  visit            

I want the top 2 of each id

id  code    date        desc 
609 AG1     2007-11-07  a                    
609 AG2     2002-11-12  a clear               

627 AONOSP  2010-02-19  a9  
627 AONSPV  2010-02-24  poessession           

---

In SQL 2005 and later, you can use ROW_NUMBER over a partition over the grouping you are interested in (id) to number each row within the group, which you can then roll into a CTE or Derived table and then filter with an outer query:

SELECT 
  x.id, x.code, x.date, x.des
FROM
(
  SELECT 
    ROW_NUMBER() OVER (PARTITION BY Id ORDER BY accounts.id) as rownum,
    id, code, date, des
  FROM accounts
    INNER JOIN accdesc 
    ON accounts.code = accdesc.act_cd 
) x -- Derived Table
WHERE 
   rownum <= 2; -- How many rows of each group

Note that you will need to take an opinion on the ordering (ORDER BY is mandatory)