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

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

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top