Results 1 to 2 of 2

Thread: limit rows

  1. #1
    Join Date
    Sep 2011
    Posts
    1

    Unanswered: limit rows

    Hi,

    I have a table like:

    Name date numb

    jan 7/11/11 3
    truus 3/10/11 6
    pieter 7/09/11 8
    sjon 1/02/11 2
    truus 4/10/11 0
    pieter 7/12/11 1
    jan 1/01/11 6
    sjon 7/11/11 7

    and so on.

    I need to limit the rows, from each name the highest 5 dates and then the sum of numb.

    please help me

    thank you

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Not sure about the "then the sum of numb" part, but if you are looking for a single value per name, then this is probably what you are looking for:
    Code:
    SELECT name
           sum(numb)
    FROM (
        SELECT name,
               date,
               numb,
               row_number() over (partition by name order by date desc) as row_num
        FROM your_table
    ) t
    WHERE row_num <= 5 
    GROUP BY name

Posting Permissions

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