Druid SQL LIMIT, OFFSET, ORDER BY

[Jason G]

Very new to Druid, sorry if this is a noob question!

I've got a simple query (non aggregation) to return paginated rows of data.

SELECT * FROM articles

LIMIT 10

OFFSET 25

How can I add an ORDER BY to this SQL query?  I want to ORDER BY a field named "title".

Thanks

[Ben Krug]

Hi Jason -

Iiuc, "ORDER BY" is really intended for aggregations, and requires a groupBy in the native query that's under the hood.

Theoretically, you could use DISTINCT on all the fields (which uses a groupBy), then order by title.  But the memory usage may be huge, performance may not be good (or it could time out).  (It would help if it weren't all the fields.)  You can try it:

SELECT DISTINCT col1, col2, ..., title, ... coln

FROM articles

GROUP BY 1,2,3,...,n

ORDER BY title

LIMIT 10

OFFSET 25

(In the group by you can use positional numbers or the actual column names.)

I'm not recommending this as a good use-case, but it may be possible, depending on the table size.  groupBy queries are resource-intensive.

--
You received this message because you are subscribed to the Google Groups "Druid User" group.
To unsubscribe from this group and stop receiving emails from it, send an email to druid-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/druid-user/50068181-eb91-48c7-9c9c-b997e370690bn%40googlegroups.com.

[Jason G]

Thanks, I will check this out.