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?

Answer 1

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