To "collapse" result rows within each group, perform an aggregation (any expression that produces a single value instead of a list) when you select result columns. For example,
select job:first job by job from people +--------------+ | job | +--------------+ | "Developer" | | "Sales" | | "Accounting" | +--------------+
Note that if you're producing multiple result columns, you need to aggregate every column in some way to collapse the group. Otherwise, the aggregations will be "spread" to match the length of the other columns:
select job:first job experience:sum age by job from people +--------------+------------+ | job | experience | +--------------+------------+ | "Developer" | 99 | | "Sales" | 28 | | "Accounting" | 43 | +--------------+------------+ select job:first job experience:age by job from people +--------------+------------+ | job | experience | +--------------+------------+ | "Developer" | 25 | | "Developer" | 40 | | "Developer" | 34 | | "Sales" | 28 | | "Accounting" | 43 | +--------------+------------+
And if all you want is a single list of results, you can use "extract":
extract first job by job from people
("Developer","Sales","Accounting"
To order results by the grouping column, you probably want to order values _before_ grouping; otherwise you're ordering _within_ groups, which appear in the result in order of their original appearance. Clauses execute right-to-left, so:
select job:first job by job orderby job asc from people +--------------+ | job | +--------------+ | "Accounting" | | "Developer" | | "Sales" | +--------------+