How to retrieve the minimum date having 3 columns (day), (month) and (year) with RSQLite?

Question

I am working with SQL in R. I want to get the minimum date by choosing the minimum value of the column 'day;, the minimum value of the column 'month' and the minimum value of the column 'year'.

I have tried this by the following code:

dbGetQuery(db, "SELECT day, month, year
                FROM surveys
                WHERE year = (SELECT MIN(year) FROM surveys);")

But my output is not one value, how can I get one value in my output and not a list of values?

Please provide sample data and desired results, as tabular text. — GMB, Nov 03 '20 at 23:31

score 0 · Answer 1 · answered Nov 04 '20 at 00:17

0

Using the test data shown we order it by year, month and day and select the first row of the sorted table.

library(sqldf)
surveys <- data.frame(year = 2001:2005, month = 5:1, day = 1:5)

sqldf("select day, month, year from surveys order by year, month, day limit 1")
##   day month year
## 1   1     5 2001

answered Nov 04 '20 at 00:17

G. Grothendieck

254,981
17
203
341

Thankyou very much, I am using the following package: library(RSQLite). Is it also possible to run this code with this package? – Elaji Nov 04 '20 at 11:59
Use the same sql. – G. Grothendieck Nov 04 '20 at 12:10
Thankyou for you reply, i have found a way to fix the problem! – Elaji Nov 04 '20 at 12:16

Parfait · Accepted Answer · 2020-11-04T19:28:59.383

0

Right now your query returns rows on the minimum year, not minimum date. Consider generating a date column by concatenating the date parts to identify minimum:

sql = "WITH sub AS (
          SELECT day, month, year
                , DATE(year || '-' ||
                       CASE 
                          WHEN length(month)=1 
                          THEN '0' || month
                          ELSE month
                       END  || '-' ||
                       CASE 
                          WHEN length(day)=1 
                          THEN '0' || day 
                          ELSE day 
                       END) AS [date]
          FROM surveys
      )

      SELECT DISTINCT day, month, year, [date]
      FROM sub
      WHERE [date] = (SELECT MIN([date]) FROM sub)"

dbGetQuery(db, sql)

Online Demo

edited Nov 04 '20 at 19:28

answered Nov 04 '20 at 05:26

Parfait

104,375
17
94
125

Thank you very much! It is giving the correct output now. – Elaji Nov 04 '20 at 12:16
1

This does not work. The date function requires 2 character month and day. Also it can give more than one row as the answer. – G. Grothendieck Nov 04 '20 at 12:21
@G.Grothendieck, good points. Adjusted accordingly. Elaji, please see edits as `MIN()` would leave out `NULL`s for incorrect `YYYY-MM-DD` generation. – Parfait Nov 04 '20 at 19:36
1

Suggest `printf('%d-%02d-%02d',year, month, day)` – G. Grothendieck Nov 04 '20 at 20:08

How to retrieve the minimum date having 3 columns (day), (month) and (year) with RSQLite?

2 Answers2