Select only the rows with the latest date in postgres

Question

I only want the latest date for each row (house) the number of entries per house varies sometimes there might be one sale sometimes multiple.

Date of sale | house number | street | price |uniqueref
-------------|--------------|--------|-------|----------
15-04-1990   |1             |castle  |100000-| 1xzytt   
15-04-1995   |1             |castle  |200000-| 2jhgkj
15-04-2005   |1             |castle  |800000-| 3sdfsdf
15-04-1995   |2             |castle  |200000-| 2jhgkj
15-04-2005   |2             |castle  |800000-| 3sdfsdf

What I have working is as follows

Creating VIEW as (v_orderedhouses) ORDER BY house number, street with date ordered on DESCso that latest date is first returned.

I then feed that into another VIEW (v_latesthouses) using DISTINCT ON (house number, street). Which gives me;

Date of sale | house number | street | price |uniqueref
-------------|--------------|--------|-------|----------
15-04-2005   |1             |castle  |800000-| 3sdfsdf
15-04-2005   |2             |castle  |800000-| 3sdfsdf

This works but seems like there should be a more elegant solution. Can I get to the filtered view in one step?

Answer 1

You do not need to create a bunch of views, just:

select distinct on(street, house_number)
  *
from your_table
order by
  street, house_number, -- those fields should be in the "order by" clause because it is in the "distinct on" expression
  date_of_sale desc;

To make this query faster you could to create an index according to the order by:

create index index_name on your_table(street, house_number, date_of_sale desc);

Do not forget to analyse your tables regularly (depending on the grown speed):

analyse your_table;

Answer 2

You can use window function row_number for this

select  * from (
   select your_table.*, row_number() over(partition by house_number order by Date_of_sale desc) as rn from your_table
) tt
where rn = 1