Hello,
I have a materialized view defined by the following:
CREATE MATERIALIZED VIEW m_ps_project_policy_device0 AS
SELECT policy_id, device_id, project_id, namespace,
metric_type, blufi_id, beacon_id, event_uuid, state, date_created,
policy_name, beacon_name, blufi_name, value, duration FROM
policy_state0
WHERE policy_id IS NOT NULL AND device_id IS NOT NULL AND
project_id IS NOT NULL AND namespace IS NOT NULL AND metric_type IS
NOT NULL AND blufi_id IS NOT NULL AND beacon_id IS NOT NULL AND
event_uuid IS NOT NULL AND state IS NOT NULL AND date_created IS NOT
NULL
PRIMARY KEY ((project_id), policy_id, device_id, date_created,
blufi_id, beacon_id, state, namespace, metric_type, event_uuid)
WITH CLUSTERING ORDER BY (date_created DESC);
This view works fine if i run a query like the following:
SELECT * FROM m_ps_project_policy_device4 where project_id=1337 and
policy_id=7331 and device_id='1234567890' limit 1;
The result of this query gives me the most recent due to the date_created
desc clustering order.
However, this query does not behave as expected:
SELECT * FROM m_ps_project_policy_device4 where project_id=1337 and
policy_id=7331 and device_id='1234567890' group by policy_id, device_id
limit 1;
The result of this query gives me the FIRST record from the partition,
which is the OLDEST record due to the clustering order desc.
Is this a natural result due to my ordering? Would I need to use a view
that has order by ASC to achieve what I want to do using the built-in group
by aggregations? I am hoping there is a way to achieve what I want to do
(getting the most recent record for each of the <project_id, policy_id,
device_id> tuples using the built-in aggregation functions.
Thanks,
Joseph Wonesh
--
This message is private and confidential. If you have received message in
error, please notify us and remove from your system.
