How to query unique values with pull query in Kafka ksqldb

Question

Running the command below:

SELECT * FROM MY_STREAM WHERE speed != 0 GROUP BY name LIMIT 10;

results to an error:

Pull queries don't support GROUP BY clauses.

Is there a way to query 10 records with the name value being different and unique across all 10 records returned?

ChristDist · Accepted Answer · 2022-07-27T01:18:37.027

You can try something like this. Also, you can include WINDOW for the specific duration.

  SELECT name, count(*) FROM MY_STREAM
  WHERE speed != 0
  GROUP BY name
  HAVING count(*) = 10
  EMIT CHANGES
  LIMIT 10;

PULL QUERY

Pulls the current value from the materialized table and terminate. The result of this statement will not be persisted in a Kafka topic and will only be printed out in the console.
The WHERE clause must contain a single value of ROWKEY to retrieve and may optionally include bounds on WINDOWSTART if the materialized table is windowed.