Aggregation with limit, order by, and count

SQL++
1

I’m trying to fetch records from an array of non-unique, secondary id’s. They are part of a composite index. For each one of these topLevelId's, there can be any number of documents. I want to only fetch up to 2 of the most recent (as valued by a numeric timestamp) so I don’t potentially return thousands of ids. I would also like to count the total number that match the topLevelId.

The simplest, functioning query I’ve been able to manage is this:

SELECT
	m.topLevelId, ARRAY_AGG(m.uid) as uids
FROM main m
WHERE
	m.table = 'activity_det'
	AND m.topLevelId IN [
      "ACHDR::1So2O7op0ZN9HG5c2d9a60c17fdb379808b227",
      "ACHDR::2gxfecLGSxMbOp5c2d9a60c17fdb379808b227"
    ]
	AND m.posted IS VALUED
GROUP BY m.topLevelId

This returns:

[
  {
    "topLevelId": "ACHDR::1So2O7op0ZN9HG5c2d9a60c17fdb379808b227",
    "uids": [
      "5c2d9a5bc17fdb379808b221"
    ]
  },
  {
    "topLevelId": "ACHDR::2gxfecLGSxMbOp5c2d9a60c17fdb379808b227",
    "uids": [
      "5c2d9a5bc17fdb379808b221"
    ]
  }
]

I’ve tried using the slice syntax to limit my uids to 2, but when their length is less than 2, it doesn’t return any values (some kind of index out of range issue, but no error). My ideal query feels like it would look like this, but it’s not correct:

SELECT
	m.topLevelId, ARRAY_AGG(m.uid)[0:2] as uids, COUNT(uids) as total
FROM main m
WHERE
	m.table = 'activity_det'
	AND m.topLevelId IN [
      "ACHDR::1So2O7op0ZN9HG5c2d9a60c17fdb379808b227",
      "ACHDR::2gxfecLGSxMbOp5c2d9a60c17fdb379808b227"
    ]
	AND m.posted IS VALUED
GROUP BY m.topLevelId
ORDER BY m.posted DESC
2 
SELECT t.topLevelId, t.total, (SELECT RAW uid FROM t.uids  AS uid LIMIT 2) AS uids
FROM ( SELECT m.topLevelId, ARRAY_AGG(m.uid) AS uids, COUNT(m.uid) AS total
       FROM main m
       WHERE m.table = 'activity_det' AND m.posted IS VALUED
             AND m.topLevelId IN [ "ACHDR::1So2O7op0ZN9HG5c2d9a60c17fdb379808b227", "ACHDR::2gxfecLGSxMbOp5c2d9a60c17fdb379808b227"]
       GROUP BY m.topLevelId) AS t;

Above one is more powerful because you can do any operation in subquery (i.e FILTER, GROUP, Aggregation, ORDER, LIMIT, OFFSET, …)

OR

SELECT t.topLevelId, t.total, t.uids[0:LEAST(2,ARRAY_LENGTH(t.uids)] AS uids
FROM ( SELECT m.topLevelId, ARRAY_AGG(m.uid) AS uids, COUNT(m.uid) AS total
       FROM main m
       WHERE m.table = 'activity_det' AND m.posted IS VALUED
             AND m.topLevelId IN [ "ACHDR::1So2O7op0ZN9HG5c2d9a60c17fdb379808b227", "ACHDR::2gxfecLGSxMbOp5c2d9a60c17fdb379808b227"]
       GROUP BY m.topLevelId) AS t;

NOTE: You can’t use m.posted because query uses Aggregation. So projection or ORDER BY can only contain expression that depends on GROUP keys or Aggregates.

1 Like
3

Thank you very much! I had a hunch sub select could do this, but I just couldn’t work it out.