Postgresql select distinct on

I was querying a log that's saved on postgresql. The database table looks like this.

CREATE TABLE IF NOT EXISTS machine_record (
    date_time TIMESTAMPTZ NOT NULL PRIMARY KEY,
    machine_id INTEGER NOT NULL REFERENCES machines(id),
    api_call VARCHAR(300) NOT NULL,
    unit_data json NOT NULL
);

so this database saves some json data. I need to find the latest log for every machine_id for a specific api_call so it should be

  • distinct
  • last date_time
  • specific api_call

so here's how I do it

select  distinct on (machine_id) machine_id , date_time, unit_data from machine_record where api_call='summary' and machine_id in ('4', '5') order by machine_id, date_time DESC;

notice that I use distinct on not just distinct. This the feature in Postgres that let us query DISTINCT ON (expression). It will keep the first row of each group that has duplicates. This almost give me the result that I want. But I want the last row instead of the first row. So do order by. And I sort date_time DESC

That give us the results

Reach me on twitter @muhajirdev
This site is built with NextJS, hosted on Netlify, and uses Github Issues as CMS