Question 191821 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Get a column that is not specified in group-by clause

Need to retrieve a column from a group-by query, and this column is not part of the group-by clause.

I have a table that receives telemetries from equipments:

COLUMNS:

  • transmission_tag varchar — this is kind of a transmission package identifier
  • equipment_id int
  • measurement int — the value measured
  • uts int — the timestamp ‘unixtime’ of the measurement

Then I need to get the most recent measurement from every equipment:

SELECT equipment_id, max(uts)
FROM telemetry
GROUP BY equipment_id
-- works OK and quick, the table has ~100mi registers and has index on (equipment_id, uts)

The question is that I need to get the "transmission_tag" related to this registers.

I tried with "WHERE EXISTS" / "WITH auxquery (…) SELECT…", but I was not able to solve this problem.

The example below retrieves exactly what I need (for testing purposes), but it is really dumb. In the real world database, it takes 1 hour to run (it does a sequential scan of all registers).

SELECT transmission_tag
FROM telemetry
WHERE CAST(equipment_id as VARCHAR) || '_' || CAST(uts as VARCHAR) IN
 (SELECT CAST(equipment_id as VARCHAR) || '_' || CAST(max(uts) as VARCHAR)
  FROM telemetry
  GROUP BY equipment_id)

The rows in the table are unique, there is only one row with (equipment_id, uts).

In case the answer is db specific, I’m using Postgres.

Tags:

3

Answers


  1. 0

    Use inner join to join your dataset with the table then get transmission_tag :

    SELECT t.equipment_id, s.max_uts, t.transmission_tag
FROM telemetry t
INNER JOIN (
  SELECT equipment_id, max(uts) AS max_uts
  FROM telemetry
  GROUP BY equipment_id
) as s on s.equipment_id = t.equipment_id and s.max_uts = t.uts
    Login or Signup to reply.
  2. 0

    I need to get the most recent measurement from every equipment […] The question is that I need to get the transmission_tag related to this registers.

    You want to filter the table, not aggregate it. In Postgres, I would recommend distinct on, which does exactly what you ask for:

    SELECT DISTINCT ON ( expression [, ...] ) keeps only the first row of each set of rows

    This gives you the entire latest row of each equipment:

    select distinct on (equipment_id) t.*
from telemetry t
order by equipment_id, uts desc
    Login or Signup to reply.
  3. 0

    You can use distinct on clause such;

    select distinct on (equipment_id)
    equipment_id, 
    transmission_tag,
    uts
  from telemetry
  order by equipment_id, uts desc;

    The query says order by equipment_id, uts desc then get first row for each distinct equipment_id. Next, you have your row and you can get any field value from that row.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search