Query N records by Item

Navigation
3

I have a history table in DB2 and I would like to do a grouping by PLACA but despite this I would have to return the last 5 records of each PLACA .

Ex: 

|ID |PLACA | DATA       | VALOR |
|12 |ABC   | 2014-08-28 | 4.50  |
|11 |DFG   | 2014-08-28 | 3.50  |
|10 |ABC   | 2014-08-27 | 2.50  |
|9  |DFG   | 2014-08-27 | 4.50  |
|8  |DFG   | 2014-08-26 | 3.50  |
|7  |ABC   | 2014-08-26 | 2.50  |
|6  |DFG   | 2014-08-25 | 4.50  |
|5  |DFG   | 2014-08-24 | 3.50  |
|4  |ABC   | 2014-08-24 | 2.50  |
|3  |DFG   | 2014-08-23 | 4.50  |
|2  |ABC   | 2014-08-23 | 4.50  |
|1  |ABC   | 2014-08-22 | 4.50  |

Should return:
ID 12, 10, 7, 4, and 2 records of PLACA ABC
and
The logs of ID 11, 9, 8, 6 and 5 of PLACA DFG

Expected return: 

|ID |PLACA | DATA       | VALOR |
|12 |ABC   | 2014-08-28 | 4.50  |
|10 |ABC   | 2014-08-27 | 2.50  |
|7  |ABC   | 2014-08-26 | 2.50  |
|4  |ABC   | 2014-08-24 | 2.50  |
|2  |ABC   | 2014-08-23 | 4.50  |
|11 |DFG   | 2014-08-28 | 3.50  |
|9  |DFG   | 2014-08-27 | 4.50  |
|8  |DFG   | 2014-08-26 | 3.50  |
|6  |DFG   | 2014-08-25 | 4.50  |
|5  |DFG   | 2014-08-24 | 3.50  |
    
asked by anonymous 28.08.2014 / 20:17

2 answers

1

Following the answer from @ CristianoAvilaSalomão I find it valid to show SQL . 

SELECT ID, PLACA, DATA, VALOR,ROWS FROM 
(
    SELECT ID, PLACA, DATA, VALOR, ROW_NUMBER() OVER(PARTITION BY PLACA ORDER BY DATA DESC) AS ROWS FROM PLACAS 
) AS PLACAS 
WHERE ROWS < 6
    
28.08.2014 / 22:49
2

I made a descending ordering process by date and then applied the following command:

ROW_NUMBER() OVER (PARTITION BY PLACA ORDER BY DATA DESC) AS ROWNUM

I made a subquery and asked to return only those items where ROWNUM <= 5

    
28.08.2014 / 22:36
MediaQueries ignored from a certain resolution How to make bematech printer cut the print in the middle?