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?
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;
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
This is what I use and it works fast(is a generic solution, as far as I tested every database software can do this):
SELECT t1.date_of_sale, t1.house_number
FROM table t1
LEFT JOIN table t2 ON (t2.house_number = t1.house_number AND t2.date_of_sale>t1.date_of_sale)
WHERE t2.pk IS NULL
GROUP BY t1.date_of_sale, t1.house_number