Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2010
    Posts
    15

    Unanswered: DisplayTop2RowsPerType

    This is a query I need for SQL Server 2000; and apparently the row_number() function does not work on SQL Server 2000.

    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'

    Again; this is a query I need for SQL Server 2000; and apparently the row_number() function does not work on SQL Server 2000.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yes you are correct, the windows aggregate functions were introduced for SQL Server 2005. Note you are 3 versions behind the latest version and SQL 2k is no longer on mainstream support. Soon it will be time to start drawing up migration plans.

    The solution is fairly well documented, but you have to know the right search terms.
    top n withing group SQL Server - Google Search
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Sep 2010
    Posts
    15
    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')


    How can I extract the top 2 rows of each Col4 type?
    Top 2, based on order of what column? (you should not base on the order insert of records)


    '2003-07-05', 100131, 715,'AA'
    '2001-03-02', 100142, 700,'AA'
    '2005-08-03', 100234, 78,'BF'
    '2002-01-02', 100357, 706,'BF'

  4. #4
    Join Date
    Mar 2010
    Posts
    15

    DisplayTop2RowsPerType

    Base on order of col2

  5. #5
    Join Date
    Sep 2010
    Posts
    15
    Using cte or subquery ...

    ;with temp as (select *, rn = ROW_NUMBER() over(PARTITION by col4 order by col2 desc) from yourTable)
    select * from temp where rn < 3

    or
    select * from (select *, rn = ROW_NUMBER() over(PARTITION by col4 order by col2 desc) from yourTable) as temp where rn < 3
    Performance are the same.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by asburym1 View Post
    This is a query I need for SQL Server 2000;
    Neither of those solutions work for 2000.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Sep 2010
    Posts
    15

    Quote:
    Originally Posted by asburym1 View Post
    This is a query I need for SQL Server 2000;
    Neither of those solutions work for 2000.
    Yeah, I missed that part ...

    For 2000 we can use join to get the same result.

    asburym1, do you have solution yet?

  8. #8
    Join Date
    Mar 2010
    Posts
    15
    No, I am still looking for a solution. The row_number statement does not work for SS2000. An example of a join a mentioned would be appreciated.

    Thanks

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Did you not click the link I posted? The very first lnik goes to here:
    Adam Machanic : Who's On First? Solving the Top per Group Problem (Part 1: Technique)
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    This will work. The result does not match the list you gave as your expected answer in your first post. Or
    Base on order of col2
    is not the right column to order by or your expected answer was not correct.
    Code:
    SELECT c.Col1, c.Col2, c.Col3, c.Col4
    FROM #customer_tbl c
    WHERE
        c.Col1 IN (SELECT TOP(2) c1.Col1
            FROM #customer_tbl c1
            WHERE c.Col4 = c1.Col4
            ORDER BY c1.Col2 ASC
            )
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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