Order a table by a set of columns (not general expressions) and limit number of rows in that order.

orderby(
  source,
  cols = NULL,
  ...,
  reverse = NULL,
  limit = NULL,
  env = parent.frame()
)

Arguments

source

source to select from.

cols

order by named columns ascending.

...

force later arguments to be bound by name

reverse

character, which columns to reverse ordering of top descending.

limit

number limit row count.

env

environment to look to.

Value

order_by node.

Details

Note: this is a relational operator in that it takes a table that is a relation (has unique rows) to a table that is still a relation. However, most relational systems do not preserve row order in storage or between operations. So without the limit set this is not a useful operator except as a last step prior to pulling data to an in-memory data.frame ( which does preserve row order).

Examples

if (requireNamespace("DBI", quietly = TRUE) && requireNamespace("RSQLite", quietly = TRUE)) { my_db <- DBI::dbConnect(RSQLite::SQLite(), ":memory:") d <- rq_copy_to(my_db, 'd', data.frame(AUC = 0.6, R2 = 0.2)) optree <- orderby(d, cols = "AUC", reverse = "AUC", limit=4) cat(format(optree)) sql <- to_sql(optree, my_db) cat(sql) print(DBI::dbGetQuery(my_db, sql)) DBI::dbDisconnect(my_db) }
#> mk_td("d", c( #> "AUC", #> "R2")) %.>% #> order_rows(., #> c('AUC'), #> reverse = c('AUC'), #> limit = 4) #> SELECT * FROM ( #> SELECT #> `AUC`, #> `R2` #> FROM #> `d` #> ) tsql_95090401506436951578_0000000000 ORDER BY `AUC` DESC LIMIT 4 #> AUC R2 #> 1 0.6 0.2