Bring the second and third line of a query

Question

Oracle-SQL

I have a chart that records the time records that each employee makes. The table contains the following information:

(TABELA QUE ESTOU FAZENDO A CONSULTA)
Contrato | Data         | Hora
1        | 10/05/2018   | 07:00
1        | 10/05/2018   | 11:30
...

I must now transpose these columns, bringing these beats as columns (TABLE AS I WANT):

Contrato   |   Data     | Hora 1 | Hora 2 | Hora 3 | Hora 4

Except I’m having a hard time getting the records, so look where I went: (QUERY CURRENT)

select distinct ponto.contrato CONTRATO,
       ponto.datamarcacao DATA,
       (select min(ponto1.horamarcacao) 
               from metaminu.rhmarcpontoreg ponto1 
                      where ponto.contrato = ponto1.contrato
                      and ponto.datamarcacao = ponto1.datamarcacao) HORA1,
        (select max(ponto2.horamarcacao) 
               from metaminu.rhmarcpontoreg ponto2 
                      where ponto.contrato = ponto2.contrato
                      and ponto.datamarcacao = ponto2.datamarcacao) HORA2,
        (select max(ponto3.horamarcacao) 
               from metaminu.rhmarcpontoreg ponto3 
                     where ponto.contrato = ponto3.contrato
                     and ponto.datamarcacao = ponto3.datamarcacao) HORA3,
        (select max(ponto4.horamarcacao) 
               from metaminu.rhmarcpontoreg ponto4 
                    where ponto.contrato = ponto4.contrato
                    and ponto.datamarcacao = ponto4.datamarcacao) HORA4
                                from metaminu.rhmarcpontoreg ponto
                                     where ponto.contrato = 7878
                                     order by ponto.datamarcacao

Of course, I kept the "max" only as an example, because the only correct fields are the first and last (I used min and max). How would you make the 2 and 3 column data popular? Any tips? Thank you.

Answer 1

It depends a lot on how your system works... and if the person hits the point more than 4x in the day... would see how it looks there...

Following the code you’ve already set for example, it could look like this:

select
t.contrato,
t.data,
(select min(e1.hora) from tabela e1 where e1.contrato = t.contrato and e1.data = t.data) entrada1,
(select min(s1.hora) from tabela s1 where s1.contrato = t.contrato and s1.data = t.data and s1.hora > (select min(e1.hora) from tabela e1 where e1.contrato = t.contrato and e1.data = t.data)) saida1,
(select max(s2.hora) from tabela s2 where s2.contrato = t.contrato and s2.data = t.data and s2.hora < (select max(e2.hora) from tabela e2 where e2.contrato = t.contrato and e2.data = t.data)) entrada2,
(select max(e2.hora) from tabela e2 where e2.contrato = t.contrato and e2.data = t.data) saida2
from tabela t

I put in the Sqlfiddle

Upshot:

CONTRATO    DATA                    ENTRADA1    SAIDA1  ENTRADA2    SAIDA2
1           2018-01-01 00:00:00.0   7           12      13          17
1           2018-01-01 00:00:00.0   7           12      13          17
1           2018-01-01 00:00:00.0   7           12      13          17
1           2018-01-01 00:00:00.0   7           12      13          17
2           2018-01-01 00:00:00.0   8           12      14          18
2           2018-01-01 00:00:00.0   8           12      14          18
2           2018-01-01 00:00:00.0   8           12      14          18
2           2018-01-01 00:00:00.0   8           12      14          18
1           2018-01-02 00:00:00.0   7           11      14          18
1           2018-01-02 00:00:00.0   7           11      14          18
1           2018-01-02 00:00:00.0   7           11      14          18
1           2018-01-02 00:00:00.0   7           11      14          18

ps. I used a field numeric as an example, for example

Answer 2

Assuming you have something like:

CREATE TABLE tb_foobar
(
    id INTEGER PRIMARY KEY
);

INSERT INTO tb_foobar( id ) VALUES ( 1 );
INSERT INTO tb_foobar( id ) VALUES ( 2 );
INSERT INTO tb_foobar( id ) VALUES ( 3 );
INSERT INTO tb_foobar( id ) VALUES ( 4 );
INSERT INTO tb_foobar( id ) VALUES ( 5 );

You can sort the table contents in descending order and retrieve only the first two records:

SELECT
  *
FROM
  tb_foobar
WHERE
  ROWNUM <= 2 
ORDER BY
  id DESC

Exit:

| ID |
|----|
|  5 |
|  4 |

Sqlfiddle: http://sqlfiddle.com/#! 4/c92f75/3