I have a file on a server that looks something like this:
library(dplyr)
library(DBI)
con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbWriteTable(con, "iris", iris)
I want to create a column that contains "ID's" - a unique "ID" for each row (something like id = 1:nrow(iris)
). Since the file is on a server, I would choose some (suitable) column (that I hope has many unique values) and use the row_number() function to create this ID:
DBI::dbGetQuery(con, "select a.* from (select *, row_number() over (order by `Petal.Length`) as rnum from iris)a limit 5;")
Sepal.Length Sepal.Width Petal.Length Petal.Width Species rnum
1 4.6 3.6 1.0 0.2 setosa 1
2 4.3 3.0 1.1 0.1 setosa 2
3 5.8 4.0 1.2 0.2 setosa 3
4 5.0 3.2 1.2 0.2 setosa 4
5 4.7 3.2 1.3 0.2 setosa 5
- But what happens if I don't have such a "suitable" column? Is it possible to make this ID column "on the spot" without specifying it over a specific choice of column?
Thank you!
CodePudding user response:
Why not just use over()
?
DBI::dbGetQuery(con, "select *, row_number() over() rnum from iris limit 5;")
Sepal.Length Sepal.Width Petal.Length Petal.Width Species rnum
1 5.1 3.5 1.4 0.2 setosa 1
2 4.9 3.0 1.4 0.2 setosa 2
3 4.7 3.2 1.3 0.2 setosa 3
4 4.6 3.1 1.5 0.2 setosa 4
5 5.0 3.6 1.4 0.2 setosa 5
CodePudding user response:
You can order over select 1
(What does it mean by select 1 from table?) -
DBI::dbGetQuery(con, "select a.* from (select *, row_number() over
(order by (select 1)) as rnum from iris)a limit 5;")
# Sepal.Length Sepal.Width Petal.Length Petal.Width Species rnum
#1 5.1 3.5 1.4 0.2 setosa 1
#2 4.9 3.0 1.4 0.2 setosa 2
#3 4.7 3.2 1.3 0.2 setosa 3
#4 4.6 3.1 1.5 0.2 setosa 4
#5 5.0 3.6 1.4 0.2 setosa 5