Signing the last SQL record

Question

Signing the last SQL record

Navigation

#1 by (2 votes)

2

I have the following SQL:

select cod_nota, cod_local, cod_produto, valor from pedido
order by
cod_nota,cod_local,cod_produto

With the result:

|cod_nota|cod_local|cod_produto| Valor | 
|  501   |   01    |   124714  | 150,00|
|  501   |   01    |   328180  | 120,00|
|  501   |   260   |   58014   | 100,00|
|  502   |   10    |   24587   | 50,00 | 
|  502   |   10    |   154547  | 20,00 |
|  502   |   10    |   154547  | 189,00|
|  502   |   15    |   24587   | 50,00 | 
|  503   |   10    |   154547  | 20,00 |
|  503   |   10    |   154547  | 189,00|

I want to have the following result:

|cod_nota|cod_local|cod_produto| Valor |ULtimo| 
|  501   |   01    |   124714  | 150,00|      |
|  501   |   01    |   328180  | 120,00|  S   |
|  501   |   260   |   58014   | 100,00|  S   |
|  502   |   10    |   24587   | 50,00 |      | 
|  502   |   10    |   154547  | 20,00 |      |
|  502   |   10    |   154547  | 189,00|  S   |
|  502   |   15    |   24587   | 50,00 |  S   | 
|  503   |   10    |   154547  | 20,00 |      |
|  503   |   10    |   154547  | 189,00|  S   |

Where am I marking an "S" in the last Local note.

sql firebird

asked by anonymous 28.03.2018 / 19:42

1 answer

Delete line breaks in an echo in PHP Invoke a method by reflection

score 2 · Accepted Answer

Assuming that last is not having product * greater in the same place / note, I make a subselect on the same table counting the larger if greater than zero is not last, if not.

select cod_nota, cod_local, cod_produto, valor  ,
       (select (case when count(*) > 0 then 'n' else 's' end) ultimo
        from  pedido p2 
        where p2.cod_local = p1.cod_local
        and   p2.cod_nota = p1.cod_nota
        and   p2.cod_produto > p1.cod_produto)
from pedido p1
order by cod_nota,cod_local,cod_produto

subselect simulates a column, it counts the notes that exist and are "bigger" than the note of the line in question, being greater than zero is not the "last", where = 0 is the "last"

I find it strange to do the product (would it have another key)?