Doubt with Select using ORDER BY

Question

I’m trying to make a SELECT so that it displays from the second table only the most recent record.

I tried to do it using GROUP BY and ORDER BY, but with the GROUP BY mine SELECT does not work, only with the ORDER BY, tried to use SELECT TOP 1 but the result did not go as expected, displaying only the most recent record of all.

Tabela A = ID_A , Produto, Tipo | Tabela B = ID_A, ID_B, Descricao, dt_renovacao

The tables are associated and I am trying to select the id, product and type of the Table A and together, id, Description and dt_renewal of the Table B, using as criterion, the record with the most recent dt_renewal of table B.

That is, for each record of Table A only a single record of Table B will be displayed which should be the most recent.

The code I made is this:

SELECT A.id_a, A.produto, A.tipo, B.id_B, B.descricao, B.dt_renovacao 
FROM A INNER JOIN B ON A.id_a = B.id_a GROUP BY B.id_a ORDER BY B.dt_renovacao DESC

Answer 1

try with subselect and max

select  
    A.id_a, A.produto, A.tipo,  
    B.id_B, B.descricao, B.dt_renovacao
from tabela_A A,tabela_B B
where B.id_B =  A.id_a
and B.dt_renovacao = (select max(B1.dt_renovacao)
                      from tabela_B B1
                      where B1.id_B = B.id_B)

Answer 2

The solution I found was to include an update date field in the first table, and then with select, the Join Inner I put in for the two dates to be the same as the code and it worked perfectly.

Answer 3

You can as our colleague Motta mentioned above, using Subselect with MAX.

Or:

SELECT A.id_a, A.produto, A.tipo, B.id_B, B.descricao, B.dt_renovacao 
FROM A INNER JOIN B ON A.id_a = B.id_a GROUP BY B.id_a ORDER BY B.dt_renovacao DESC
OFFSET 0 ROWS 
FETCH NEXT 5 ROWS ONLY

Where the 5 would be the number of values you want to return.

Answer 4

--Resultado desejado
select  tmp.*, b.descricao 
from    (
        select    distinct(a.id_a)
                , a.produto
                , a.tipo
                , max(b.dta_renovacao) as dta_renovacao
        from    tab_a a
                left join tab_b b on
                    b.id_a = a.id_a 
        group by a.id_a, a.produto, a.tipo
        ) as tmp
        inner join tab_b as b on
            b.dta_renovacao = tmp.dta_renovacao
;

--Query mais elegante mas sem uma das colunas
select    distinct(a.id_a)
                , a.produto
                , a.tipo
                , max(b.dta_renovacao) as dta_renovacao
        from    tab_a a
                left join tab_b b on
                    b.id_a = a.id_a 
        group by a.id_a, a.produto, a.tipo

View online http://tpcg.io/iLzByAzk

CREATE TABLE tab_a (
    id_a serial NOT NULL,
    produto varchar NULL,
    tipo varchar NULL
);

CREATE TABLE tab_b (
    id_b serial NOT NULL,
    id_a int4 NOT NULL,
    descricao varchar NULL,
    dta_renovacao timestamp NOT NULL
);


INSERT INTO tab_a (id_a,produto,tipo) VALUES 
(1,'Produto 1','Tipo 1')
,(2,'Produto 2','Tipo 2')
,(3,'Produto 3','Tipo 3')
,(4,'Produto 4','Tipo 4')
;

INSERT INTO tab_b(id_b,descricao,dta_renovacao,id_a) VALUES 
(1,'descricao 1 1','2020-04-15 00:00:01.000',1)
,(2,'descricao 1 2','2020-04-15 00:00:02.000',1)
,(3,'descricao 1 3','2020-04-15 00:00:03.000',1)
,(4,'descricao 2 1','2020-04-15 00:00:04.000',2)
,(5,'descricao 2 2','2020-04-15 00:00:05.000',3)
;

Answer 5

Good afternoon, I would try to do so:

select  
    A.id_a, A.produto, A.tipo,  
    B.id_B, B.descricao, B.dt_renovacao

from tabela_A A 

left join tabela_B B On B.id_B =  A.id_a

ORDER BY B.dt_renovacao DESC