Query of N records by Item

Asked

Viewed 101 times

3

I have a history table on DB2 and would like to carry out a PLACA however 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:
The records of ID 12, 10, 7, 4 and 2 of PLACA ABC
and
The records 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  |
sql select query

  • What return format do you expect, Cristiano? Serialized ids (e.g. | ABC | 12, 10, 7, 4, 2 |)? Individual recordsets? Add to your question an example of the desired return.

  • Which database?

2 answers

2

I did a process of decreasing ordering by date then applied the following command:

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

I set up a subquery and asked to return only the items where ROWNUM <= 5

1


Following @Cristianoavilasolomon’s reply I think it is valid to show the SQL complete.

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

Browser other questions tagged sql select query

You are not signed in. Login or sign up in order to post.