Creating an "ID" variable "On the Spot"

Question

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!

score 2 · Answer 1 · answered Jun 23 '22 at 02:24

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

score 2 · Accepted Answer · answered Jun 23 '22 at 02:28

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

Creating an "ID" variable "On the Spot"

2 Answers2

Linked