I have a table full of addresses. In this table, multiple copies of the same address or stored, but they become deprecated or considered old addresses. So what I need is the current address, and the current address is the one that has the highest ID [max(id)]. I'm trying to retrieve the object but I can't get the clause correct; something like:
select add from addresses as ad where state = 'tx' and max(add.id)
I realize that syntactically that is incorrect, but I've tried several approaches and haven't gotten it. I have no control over the data model, and I'd prefer not to join to myself (unless that's the only way).
TIA

give this query a try:

You should also try to determine what makes an address a duplicate of another one because now the only thing that is common between the query and the subselect is the 'state' field

hope this helps

Koen Weyn