Sorting and Limiting Results

LIMIT: controlling the number of primary objects

LIMIT restricts the number of primary objects (those from FROM) returned by the query. It requires a mandatory ORDER BY that determines which ones to keep.

-- The 50 most recently created contacts
SELECT email, firstname, lastname
FROM contact
LIMIT 50 ORDER BY RECENTLY_CREATED

-- The 100 oldest closed-won deals
SELECT dealname, amount
FROM deal
WHERE dealstage = 'closedwon'
LIMIT 100 ORDER BY OLDEST_CREATED

Sort options for LIMIT

Option	Meaning
`RECENTLY_MODIFIED`	Most recently modified first
`OLDEST_MODIFIED`	Least recently modified first
`RECENTLY_CREATED`	Most recently created first
`OLDEST_CREATED`	Least recently created first

Technical note: LIMIT is applied on the HubSpot side, before any data is downloaded. It's the most efficient tool to reduce the volume of data processed — and therefore execution time.

TAKE: limiting associated objects

TAKE is the equivalent of LIMIT but for objects inside a WITH block. It allows keeping only the first N associated objects, according to a sort criterion.

-- The 3 largest deals per company
SELECT name, domain
FROM company
WITH ASSOCIATED deal AS top_deals
  SELECT dealname, amount
  WHERE dealstage = 'closedwon'
  TAKE 3 ORDER BY amount DESC

-- The first contact created per company
SELECT name, domain
FROM company
WITH ASSOCIATED contact AS first_contact
  SELECT email
  TAKE 1 ORDER BY OLDEST_CREATED

Sort options for TAKE

TAKE supports all LIMIT sorts, plus sorts by property value:

Option	Meaning
`RECENTLY_MODIFIED`	Most recently modified
`OLDEST_CREATED`	Oldest created
`amount DESC`	By amount descending
`amount ASC`	By amount ascending
`closedate DESC`	By close date descending
`<property> ASC/DESC`	Sort by any property

The important difference between LIMIT and TAKE

	LIMIT	TAKE
Applies to	Primary objects (FROM)	Associated objects (WITH)
Evaluated	HubSpot side (server)	RevOpsQL side (client)
Reduces API calls?	Yes	No — all associated objects are downloaded
Supports property sorts?	No	Yes

Performance tip: Use LIMIT first to reduce overall volume. TAKE is useful for structuring data (top N per group), but does not speed up downloading.