Hi all, I am bumping this email hoping that it can reach a larger audience.
Thanks, Joseph On Tue, Feb 12, 2019 at 11:45 AM Joseph Wonesh <joseph.won...@sticknfind.com> wrote: > 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.
