SQL - select the penultimate date -

SQL - select the penultimate date -

i have table has [archivedate] column this:

archivedate 2014-10-06 2014-10-06 2014-10-06 2014-10-01 2014-10-01 2014-10-01 2014-10-01 2014-05-22 2014-05-22

i want select penultimate date, when use:

select max([archivedate]) -1 'previousweek' [pipelinearchive]

i 2014-10-05 (which doesn't exist in column), rather 2014-10-01.

i can't figure out how code select "last one"; help much appreciated!

thank you.

you need sort on archivedate in descending order, skip 1 record, , take next one. example, in sql server 2012 way:

select distinct [archivedate] [pipelinearchive] order [archivedate] desc offset (1) rows fetch next (1) rows

demo.

sql