lundi 1 décembre 2014

Query with column data for a max


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.





Aucun commentaire:

Enregistrer un commentaire