wsp1morlet wsp1morlet - 5 months ago 22
SQL Question

Executing mySQL queries in R with MonetDB

I am new to SQL and am using MonetDB to load a large file into R studio. I have loaded my data as a db using monetDB, and would now like to execute the R code below on the data in this database:

my_selection <- db_data %>% group_by(id) %>%
tally(. , sort = TRUE) %>%
top_n(100) %>%
select(id)


Basically, I want to group my data by "id", tally and sort it, and select the 100 largest elements in it. What would be an equivalent of this in SQL?

I am executing queries in the following way in R:

my_selection <- dbGetQuery(connection,"SELECT * FROM my_table [INSERT REST OF CODE HERE]")

Answer

That's depends on the DBMS you're using ,

SQL-Server :

SELECT TOP 100 id,sum(YourOtherColumn) as sum_c
FROM YourTable
GROUP BY id
ORDER BY sum_c DESC

MySQL :

SELECT id,sum(YourOtherColumn) as sum_c
FROM YourTable
GROUP BY id
ORDER BY sum_c DESC
LIMIT 100

If it's something else, tell me and I'll edit the answer.