4
I have a postgresql database with a table like this:
id_usuario | dt_atualizacao | atualizacao | outros_dados
123 | 01-01-2001 | 0 | abc
123 | 01-01-2005 | 1 | abe
123 | 01-01-2012 | 1 | acd
123 | 01-01-2018 | 1 | acc
124 | 01-01-2017 | 0 | acj
124 | 01-01-2018 | 1 | agy
125 | 01-01-2018 | 0 | awe
What happens is this: in this application, when registering a user for the first time, it leaves the field atualizacao = 0
and put the current date.
but when an update is made in this register (let’s say I changed the field outros_dados
), instead of updating the current record, it creates a new record with new date and field atualização = 1
.
What I need is a select that takes all user id, without repeating it and being the most current one. In the above example, I would need the following result:
id_usuario | dt_atualizacao | atualizacao | outros_dados
123 | 01-01-2018 | 1 | acc
124 | 01-01-2018 | 1 | agy
125 | 01-01-2018 | 0 | awe
Any tips on how to do it?
BS: before anyone comes here asking "where is the code", I already inform that I have no idea how to generate a select so, so I did not post.
is that it’s not my system. I’m just pulling information from it, so I can’t change it
– Italo Rodrigo
Table not a column of sequential numbering? Or any other where a correct ordering can be done?
– João Martins
worse than not. is a huge database (more than 700 tables) that link to each other, and I’m having to figure out which one has the information I need. for the time being with this one (and posted only an example of her)... but thanks, I will use your example code to see if I can get something here
– Italo Rodrigo
The code will work, but it may not return the 100% correct result. Returns to last date, but may not necessarily be the last update (because we have no way of knowing!). I added only the question of
atualizacao = 1
.– João Martins
but I think picking up the last date works yes, because there is never more than one update on the same day. I will be this afternoon
– Italo Rodrigo
So ok, in principle it will work at 100%!
– João Martins
In this case, just remove the clause
where
, because keeping it will not appear the line with the125
as initially desired.– Dudaskank
worked correctly, I’m just adapting here and linking with other tables. if you can, clarifies me two things: 1. what’s the difference between
distinct
anddistinct on
? 2. has some way of not ordering first by the field ofdistinct on
? need to order by another field. hug– Italo Rodrigo
The
DISTINCT
only removes duplicates of the result, while theDISTINCT ON
finds the first record in a group (which is your case). The field you put inDISTINCT ON
is the one where you want to get the first group registration, if you change that the results will be different! But you can "play" a little bit with that, see where it goes.– João Martins
@Italo Rodrigo, if you need to order in another field, then use my answer. Further information on DISTINCT and DISTINCT ON can be found in the documentation: https://www.postgresql.org/docs/10/static/sql-select.html#SQL-DISTINCT
– Dudaskank