0

In Postgres, I have a table like this:

    Column    |            Type             |                                 Modifiers
--------------+-----------------------------+--------------------------------------------
 problem_pk   | bigint                      | not null default nextval(...)
 version      | bigint                      | not null default 1
 problem_type | e_problem_type              | not null
 building_fk  | bigint                      | not null
 dpoint_fk    | bigint                      |
 native_name  | character varying(256)      |
 event_date   | timestamp without time zone | not null default now()
 count        | bigint                      |

and e_problem_type looks like this:

\dT+ e_problem_type;
                                         List of data types
  Schema  |      Name      | Internal name  | Size |             Elements             | Description
----------+----------------+----------------+------+----------------------------------+-------------
 ecorithm | e_problem_type | e_problem_type | 4    | nil                             +|
          |                |                |      | empty_string                    +|
          |                |                |      | nan                             +|
          |                |                |      | dash                            +|
          |                |                |      | other_bad_data                  +|
          |                |                |      | unmapped                        +|
          |                |                |      | missing_header                  +|
          |                |                |      | low_data_volume                 +|
          |                |                |      | attachment_with_no_valid_headers |


And I have a query like this:

select
    problem_type, native_name, max(event_date)
from
    point_problem_history
where
    building_fk = 3
and
    problem_type = 'nil'
group by
    native_name, problem_type
order by native_name;

I want to add the "count" field to my output, but have been unsuccessful in figuring out how to do this. If I add it, I have to group by it and I end up with duplicate native names. What I want is, for each native name, the value of the 'count' field for the most recent event_date for that native name and problem type = (the string 'nil'). I've tried a lot of different queries and subqueries with no success. Any and all help appreciated.

2

You can use DISTINCT ON:

select distinct on (native_name)
    'nil' as problem_type, native_name, event_date, count
from
    point_problem_history
where
    building_fk = 3
and
    problem_type = 'nil'
order by
    native_name, event_date desc ;

which does exactly what you want. Similar to GROUP BY, it will keep one row per distinct value of native_name but - differently that group by - you don't need to use max and min functions. The row selected will be the first according to order by event_date desc, so the maximum event date per native name.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.