CODEWITHSUNDEEP
sqlms-access
Michiel Soede
Michiel Soede

Reputation: 55

SQL subquery Access - one before last

Hopefully someone can help me with this query I want to make in Access. I have a table with three columns and a value column. It concerns financial data of which more than one version is there, so for B2C / SI_01 / INP01 I have for example four versions with different timestamps. How can I by means of a sub query, query the "before last" timestamp of the combinations of B2C / SI_01 / INP01? I tried it a couple of times with top n-2, descending sort order and not like max time stamp but I seem not to get the sql correct.

enter image description here

So far I my code is:

SELECT  [N-1], SI_Initiative, LineCode, Timestamp
FROM TBL_VC_DATA t1
WHERE t1.Timestamp = 
    (SELECT Max(t2.Timestamp) 
     FROM TBL_VC_DATA t2
     WHERE t2.timestamp<>t1.timestamp
     AND t2.[N-1]=t1.[N-1]
     AND t2.SI_Initiative=t1.SI_Initiative
     AND t2.LineCode=t1.LineCode);

Thanks in advance,

Michiel

Upvotes: 1

Views: 49

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521103

One option is to use TOP 2 in your query, sorted descending by the timestamp, and then exclude the max (last) timestamp record.

SELECT TOP 2 [N-1], SI_Initiative, LineCode, Timestamp
FROM TBL_VC_DATA t1
WHERE t1.Timestamp <> (SELECT MAX(t2.Timestamp)
                       FROM TBL_VC_DATA t2
                       WHERE t2.timestamp <> t1.timestamp AND
                             t2.[N-1] = t1.[N-1] AND
                             t2.SI_Initiative = t1.SI_Initiative AND
                             t2.LineCode = t1.LineCode)
ORDER BY t1.Timestamp DESC

Your addition in the subquery's WHERE clause of WHERE t2.timestamp <> t1.timestamp is good, because it will allow the above query to return both records should there be a time between the last and second-to-last record with regard to timestamp value.

Upvotes: 1

Related Questions