Results 1 to 2 of 2

Thread: Top2rowsInGroup

  1. #1
    Join Date
    Mar 2010
    Posts
    15

    Cool Unanswered: Top2rowsInGroup

    From the following created table #customer_tbl:

    CREATE TABLE #customer_tbl(Col1 datetime, Col2 int, Col3 int, Col4 char(2))

    INSERT INTO #customer_tbl Values('2003-07-05', 100131, 715,'AA')
    INSERT INTO #customer_tbl Values('2001-03-02', 100142, 700,'AA')
    INSERT INTO #customer_tbl Values('2004-09-02', 100154, 38,'AA')
    INSERT INTO #customer_tbl Values('2005-08-02', 100113, 73,'AA')
    INSERT INTO #customer_tbl Values('2005-08-03', 100234, 78,'BF')
    INSERT INTO #customer_tbl Values('2002-01-02', 100357, 706,'BF')
    INSERT INTO #customer_tbl Values('2003-01-02', 100052, 706,'BF')
    INSERT INTO #customer_tbl Values('2004-01-02', 100291, 706,'BF')
    INSERT INTO #customer_tbl Values('2004-06-09', 100317, 390,'DT')
    INSERT INTO #customer_tbl Values('2004-06-09', 100139, 700,'DT')
    INSERT INTO #customer_tbl Values('2005-08-03', 100154, 38,'DT')
    INSERT INTO #customer_tbl Values('2006-07-04', 100113, 73,'DT')
    INSERT INTO #customer_tbl Values('2007-03-03', 100234, 78,'KC')
    INSERT INTO #customer_tbl Values('2003-01-02', 100348, 706,'KC')
    INSERT INTO #customer_tbl Values('2004-11-12', 100556, 706,'KC')
    INSERT INTO #customer_tbl Values('2005-01-04', 100741, 706,'KC')

    How can I extract the top 2 rows of each Col4 type?

    For example:

    '2003-07-05', 100131, 715,'AA'
    '2001-03-02', 100142, 700,'AA'
    '2005-08-03', 100234, 78,'BF'
    '2002-01-02', 100357, 706,'BF'
    '2004-06-09', 100317, 390,'DT'
    '2004-06-09', 100139, 700,'DT'
    '2007-03-03', 100234, 78,'KC'
    '2003-01-02', 100348, 706,'KC'

  2. #2
    Join Date
    Jun 2008
    Posts
    19
    You should be able to use the ROW_NUMBER() function with the PARTITION clause. For example, add the row_number function to your query, partition over the Col4 column and then from that result set select anything with a row_number column <= 2. Make sure it is ordered the way you want, something like:


    SELECT *
    FROM
    (
    SELECT ROW_NUMBER() OVER(PARTITION BY Col4 ORDER BY Col4) AS RowNum, *
    FROM #customer_tbl
    ) a
    WHERE RowNum <= 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
  •