CODEWITHSUNDEEP
sqloracle-databasefrequencyoracle-sqldeveloper
user963084
user963084

Reputation: 13

SQL query for last entries in a period

I'm trying to write a SQL query for Oracle SQL in order to retrieve the last records for a certain period frequency. For example, say the frequency is Quarterly, (I'd also like monthly and annually to work), I can provide the start dates and end dates for the quarters if necessary, but I need to retrieve the last entry within each quarter. How can I do this? I've had limited luck so far without writing lots of subqueries.

Upvotes: 1

Views: 740

Answers (2)

Steve
Steve

Reputation: 399

SELECT col1, col2, col3...colN
FROM TableA
WHERE  (colX = (SELECT     MAX(Date) AS LastDate
    FROM          TableA 
    WHERE      QuarterDate Between (BeginningDate AND EndingDate)

colX is the date column that is your date you need to be checking if in the quarter.

I think that should work.

Upvotes: 0

user359040
user359040

Reputation:

Try something like:

select * from
(select t.*,
        row_number() over (partition by trunc(date_field, 'MON') 
                           order by date_field desc) rn
 from my_table t)
where rn = 1

for months. (Use 'Q' or 'Y' instead of 'MON' in the trunc clause for quarters or years.)

Upvotes: 2

Related Questions