Bring the second and third line of a query

Navigation
0

Oracle-SQL

I have a table that records the point records that each employee does. The table gives 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 now need to transpose these columns, bringing these beats as columns (TABLE AS I WANT): 

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

But I find it difficult to bring the records, see where I went: (CURRENT QUERY) 

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, since the only correct fields are the first and last (I used min and max). How would you use popular 2 and 3 column data? Any tips? Thank you.

    
asked by anonymous 25.05.2018 / 00:13

2 answers

0

It depends a lot on how your system works ... and if the person hits the point more than 4x in the day ... would have to see how that goes ...

Following the code that you have already put as an 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 it in SQLFiddle

  

Result: 

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 numeric field as time, just an example

    
25.05.2018 / 00:44
0

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 contents of the table in descending order and retrieve only the first two records: 

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

Output: 

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

SQLFiddle: link

    
25.05.2018 / 00:54
NdsManager Android error Email validation using hide and show