6

I have a table power with a datetimetz field called sample_time and a column called amp_hours.

The amp_hours field gains a record about every two minute and is reset every night at midnight.

I would like to see sample_time and amp_hours for the last record of every day.

I'm very new to SQL so I may be overlooking an obvious answer.

I saw this post on how to select the last record of a group but I'm not familiar enough with SQL to get it to work for datetimes:

I thought to use lead() or lag() to compare the date of a record with the next record but I'm using postgresql 8.3 and I think windowing was introduced in 8.4.

Community
  • 1
  • 1
RyanN
  • 740
  • 8
  • 20

1 Answers1

13

Try this:

SELECT DISTINCT ON (sample_time::date) sample_time, amp_hours
FROM power
ORDER BY sample_time::date DESC, sample_time DESC;
Matthew Wood
  • 16,017
  • 5
  • 46
  • 35
  • 1
    Nice solution, the first `DESC` is optional. – Erwin Brandstetter Aug 14 '12 at 16:56
  • Thanks, that is clever! If I understand this correctly, the `DISTINCT ON (sample_time::date)` makes sure only one record per day is returned and the ORDER BY does the rest. If I omit the second DESC i get the first record of every day. The first DESC just makes the most recent records first. – RyanN Aug 14 '12 at 17:07
  • @RyanN: [`DISTINCT`](http://www.postgresql.org/docs/current/interactive/sql-select.html#SQL-DISTINCT) automatically orders by the (to be) distinct columns. You can add an explicit `ORDER BY` - that cannot disagree with `DISTINCT` - with additional columns and/or `DESC` instead of the default `ASC` for the distinct columns. – Erwin Brandstetter Aug 14 '12 at 17:22
  • 2
    @ErwinBrandstetter: `DISTINCT` can generate the distinct rows through different plans, only some of which leave the data in a predictable order. If it chooses to use a hash table instead of a sort, the order will be hard to predict. If you want results to be in some order, it is always a good idea to specify `ORDER BY` rather than relying on implementation details. @RyanN: Without the `ORDER BY` it might have returned the most recent rows *this time*; don't count on that. – kgrittn Aug 15 '12 at 12:29
  • @ErwinBrandstetter: In addition to kgrittn's comments, DISTINCT ON is different than DISTINCT in the point that the list of columns inside the ON() is required in the ORDER BY clause. Otherwise this error is thrown: "SELECT DISTINCT ON expressions must match initial ORDER BY expressions" – Matthew Wood Aug 16 '12 at 16:05
  • @MatthewWood: My comment says just that: `explicit ORDER BY - that cannot disagree with DISTINCT`. You may want to have a look at one of the [dozens of answers](http://stackoverflow.com/search?q=user%3A939860+%22distinct+on%22&submit=search) I posted on the matter to verify that I know. Not sure why Kevin felt the need to address me. Neither, why you do. Maybe I was too brief - but hey, that's where the added link to the manual comes in. – Erwin Brandstetter Aug 16 '12 at 16:28
  • @ErwinBrandstetter: Sorry about that, your statement seemed to apply on to DISTINCT, not DISTINCT ON (which has different rules for ORDER BY). I believe that was the source of my misunderstanding. Please accept my apology as no offense was intended. – Matthew Wood Aug 16 '12 at 17:07
  • My comment was not as clear as it should have been. Should have spelled out DISTINCT *ON* to be clear. I see that now and bow to your politeness. – Erwin Brandstetter Aug 17 '12 at 03:03