Select the most recent record of a given table - PostgreSQL

Question

Select the most recent record of a given table - PostgreSQL

Navigation

#1 by (3 votes)

0

I need to select the most recent record of my Estoque if the data column is different, I was able to bring the oldest one when the dates are different:

SELECT codigosuprimento, numeroserie, max(data)
FROM public.estoque where usado = '1' and numeroserie = '1201607048733' 
group by codigosuprimento, numeroserie;

But when the column data is equal, I want it to bring all the records that dates are equal, for example:

Iwouldbringthetwolinessincethedatesarethesame,butwhenso:

It would bring the date 2017-08-18 to be the most current.

sql postgresql select

asked by anonymous 31.10.2017 / 12:33

1 answer

Validation function receives number 0 or 1 How to prevent a mode from appearing?

score 3 · Accepted Answer

A solution can be a subselect with MAX

SELECT codigosuprimento, numeroserie, data
FROM public.estoque p1----faz uma primeira instancia da tabela 
where usado = '1' 
and numeroserie = '1201607048733' 
-- este subselect procura a maior data da mesma chave do registro
and data = (SELECT max(data)
            --faz uma segunda instancia da tabela 
            --renomeia o "alaias" para diferenciar
            FROM public.estoque p2
            where p2.usado       = p1.usado
            and   p2.numeroserie = p1.numeroserie)