CODEWITHSUNDEEP
r
Citizen
Citizen

Reputation: 121

How to keep initial row order

I have run this SQL sentence through the package: sqldf

SELECT A,B, COUNT(*) AS NUM 
FROM DF
GROUP BY A,B

I have got the output I wanted, but I would like to keep the initial row order. Unfortunately, the output has a different order.

For example:

> DF
   A B   C D
1 11 2 432 4
2 11 3 432 4
3 13 4 241 5
4 42 5   2 3
5 51 5 332 2
6 51 5 332 1
7 51 5 332 1
> sqldf("SELECT A,B,C,D, COUNT (*)  AS NUM
+        FROM DF
+        GROUP BY A,B,C,D")
   A B   C D NUM
1 11 2 432 4   1
2 11 3 432 4   1
3 13 4 241 5   1
4 42 5   2 3   1
5 51 5 332 1   2
6 51 5 332 2   1

As you can see the row order changes, (row number 5 and 6). It would be great if someone could help me with this issue.

Regards,

Upvotes: 2

Views: 62

Answers (1)

akrun
akrun

Reputation: 887158

If we need to use this with sqldf, use ORDER.BY with names pasted together

library(sqldf)
nm <- toString(names(DF))
DF1 <- cbind(rn = seq_len(nrow(DF)), DF)
nm1 <- toString(names(DF1))
fn$sqldf("SELECT $nm, COUNT (*)  AS NUM
    FROM DF1
    GROUP BY $nm ORDER BY $nm1")
#   A B   C D NUM
#1 11 2 432 4   1
#2 11 3 432 4   1
#3 13 4 241 5   1
#4 42 5   2 3   1
#5 51 5 332 2   1
#6 51 5 332 1   2

Upvotes: 2

Related Questions