CODEWITHSUNDEEP
sqlsql-server
ertuu85
ertuu85

Reputation: 79

Finding 2nd to last date with SQL

I'm trying to find the 2nd to last date using the below query to get all unique dates.

SELECT DISTINCT CAST(Entrydatetime AS DATE)
FROM   [table_name]

This returns

2017-11-15
2017-11-16
2017-11-17
2017-11-20

The [table_name] contains many rows, many that are all on the same dates listed above. (200 records with '2017-11-20' and 180 with '2017-11-17' etc..)

So is there a way to just grab the 2nd to last item given from the above query?

If this were an array I could do something like '$array[-2]'

So in this instance it would pull 2017-11-17.

Any help would be appreciated

Upvotes: 1

Views: 117

Answers (3)

Zorkolot
Zorkolot

Reputation: 2017

Here's an example using a CTE. I think the DISTINCT EntryDate has to be taken- cast to date to remove time- then the rownumber applied. Notice I am using two different times for 2017-11-20, 12:00:00 and 13:00:00. The ORDER BY cannot do this correctly if you have DISTINCT in the select... this requires a subquery.

DECLARE @tablename TABLE (Entrydatetime datetime)
INSERT INTO @tablename VALUES ('2017-11-15 12:00:00')
                             ,('2017-11-16 12:00:00')
                             ,('2017-11-17 12:00:00')
                             ,('2017-11-20 12:00:00')
                             ,('2017-11-20 13:00:00')
;
WITH cte AS
(
SELECT ROW_NUMBER() OVER(ORDER BY EntryDate DESC) [theOrder]
      ,dT.EntryDate
  FROM (
         SELECT DISTINCT CAST(Entrydatetime as date) AS EntryDate
           FROM @tablename
       ) AS dT
)

SELECT cte.EntryDate FROM cte WHERE theOrder = 2

Gives output:

EntryDate
2017-11-17

Upvotes: 0

Hadi
Hadi

Reputation: 37313

If using SQL Server, you can use the following query:

with cte as
(
select DISTINCT Convert(Date,Entrydatetime) ,
ROW_NUMBER() over (order by Convert(Date,Entrydatetime) DESC) as rn
from [table name]
) 
select * from cte where rn=2

Or you can do it another way

SELECT MAX(Convert(Date,Entrydatetime)) 
FROM [table name] 
WHERE Convert(Date,Entrydatetime) < (SELECT MAX(Convert(Date,Entrydatetime) ) FROM [table name])

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

You can use ROW_NUMBER to rank your dates:

select dt
from
(
  select distinct 
    cast(entrydatetime as date) as dt,
    row_number() over (order by cast(entrydatetime as date) desc) as rn
  from table_name
) ranked
where rn = 2;

Upvotes: 2

Related Questions