Quantile not working with MSSQL

[Karl-Ludwig]

Calculation of quantile not working with MSSQL - alternative with direct sql-statement currently required.

# insert reprex here

library(DBI)
library(dplyr)
library(dbplyr)

#Connection to MSSQL-Server
con <- dbConnect(odbc::odbc(), .connection_string = "Driver={SQLServer};Server= myserver;Trusted_Connection=Yes;timeout = 10")
#copy mtcars to database
copy_to(con,mtcars,in_schema("myschema","mtcars"),temporary=FALSE)
#query mtcars
mt <- tbl(con,in_schema("myschema","mtcars"))

#calculate quantile
qu<-mt%>%summarise(q50=quantile(hp,0.5))
show_query(qu)
#<SQL>
#SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY "hp") AS "q50"
#FROM "myschema"."mtcars"

collect(qu)
#Error: nanodbc/nanodbc.cpp:1617: 00000: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]                                                          #[Microsoft][ODBC Driver 17 for SQL Server][S L Server]                                    
#<SQL> 'SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY "hp") AS "q50"
#FROM "myschema"."mtcars"'
#Errror Message SQL-Server: The function 'PERCENTILE_CONT' must have an OVER clause. Over clause and distinct are missing

#calculate quantile based on df:
mtcars%>%summarise(q50=quantile(hp,0.5))
#  q50
#1 123
#it works

#Alternative with SQL-Server - it works
mt%>%summarise(q50=sql("PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY hp) OVER()"))%>%distinct()
# Source:   lazy query [?? x 1]
# Database: Microsoft SQL Server 15.00.4083[DExxxxx@yyyyyyy/mydatabase]
#    q50
#  <dbl>
#1   123