I have a table with the equivalent of:
group_id integer REFERENCES <some other table> item_id integer REFERECNES <some other table> timestamp date data text sort_order text
I want to get the latest
data for every item before some date. I.e. the maximum
timestamp less than some value. I also want these rows sorted on
After som Googling I find that
SELECT DISTINCT ON is what I want, so I get something like
SELECT DISTINCT ON (item_id) data FROM [this table] WHERE group_id = [some value] AND timestamp < [some value] ORDER BY item_id, timestamp DESC
Now, I actually want the data sorted on
DISTINCT ON requires the
ORDER BY to be listed first.
One solution that comes to mind is to wrap all of this in an outer
SELECT that will
ORDER BY sort_order. Is that a good efficient solution, or is there a better "correct" way to do this.
(Or, am I in fact not even close...)