select second lowest date in table-CodePudding

I want to filter the second lowest date. I´m familiar with the min/max functions, but they are not applicable here.

DF<-tibble::tribble(
         ~date,
  "2019-01-01",
  "2019-01-02",
  "2019-01-03",
  "2019-01-04",
  "2019-01-05",
  "2019-01-06",
  "2019-01-07",
  "2019-01-08",
  "2019-01-09",
  "2019-01-10",
  "2019-01-11",
  "2019-01-12",
  "2019-01-13",
  "2019-01-14"
  )

DF$date<-as.Date(DF$date)

Desired output:

2009-01-02

Anyone? :)

CodePudding user response：

A little lateral thinking gives this solution using the tidyverse.

The logic is to sort the dates from lowest to highest (arrange), select the earliest two (head) and then take the latest of the two selected (max).

> DF %>% arrange(date) %>% head(2) %>% pull(date) %>% max()
[1] "2019-01-02"

CodePudding user response：

Using order in this way.

DF$date[order(as.Date(DF$date)) == 2]
# [1] "2019-01-02"

CodePudding user response：

 SELECT
 table2.ID,
 table2.DNUMBER,
 table1.DNUMBER,
 MAX(table1.FDATE) AS LATESTDATE,
 (
 SELECT MAX(table1.FDATE) 
 FROM table1 
 WHERE table2.DNUMBER = table1.DNUMBER 
 AND table1.FDATE < (
    SELECT MAX(table1.FDATE) 
    FROM table1 
    WHERE table2.DNUMBER = table1.DNUMBER
   )
   ) AS SECONDLATESTDATE
   FROM table2
   LEFT OUTER JOIN table1 ON table2.DNUMBER = table1.DNUMBER
   GROUP BY table2.DNUMBER

CodePudding user response：

You could use sort, head and tail like so.

library(tidyverse)

DF$date %>% sort() %>% head(2) %>% tail(1)

#[1] "2019-01-02"