1

I'm using DB2 database. I have a table that contains the following columns:

Group | Name | Date | Value

I want to select the 10 last values (ordered by date) for each Group. How can I do that in DB2 ? Many thanks.

UPDATE :

Here is what I did so far : 

select Group, DATE
from MyTable
ORDER BY Group, DATE 
FETCH FIRST 10 ROWS ONLY

This sql command return the first 10 rows of the results and I want to get 10 last results for each Group.

deltascience
  • 3,321
  • 5
  • 42
  • 71
  • your query with order by desc, `FETCH FIRST 10 ROWS ONLY` – amdixon May 13 '15 at 09:28
  • change your `order by` clause to use descending `desc` – amdixon May 13 '15 at 09:33
  • I think you didn't understand the problem. I don't want to get 10 last rows of all the records but the 10 last of each group. Even if I add `desc` it returns only the last 10 rows of the global result – deltascience May 13 '15 at 09:35
  • You may want to read this; http://stackoverflow.com/questions/176964/select-top-10-records-for-each-category – dasothem May 13 '15 at 09:44
  • and also this [how-to-select-the-firstleastmax-row-per-group-in-sql](http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/). there is a piece there on how to get top N rows per group – amdixon May 13 '15 at 09:46

1 Answers1

4

You can't do this with a simple order by clause; you need to use an OLAP function:

with ordered as (
  select group, 
         date, 
         row_number() over (partition by group order by date desc) as date_rank
    from mytable
)
select group, date 
  from ordered 
  where date_rank <= 10