Results 1 to 3 of 3

Thread: TopInAGroup

  1. #1
    Join Date
    Mar 2010
    Posts
    15

    Cool Unanswered: TopInAGroup

    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
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I think something like:

    Code:
    select * from customer_tbl where row_number() over (partition by col4 order by col3) <= 2
    Andy

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by asburym1 View Post

    How can I extract the top 2 rows of each Col4 type?
    How do you define "top two" out of many?

Posting Permissions

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