Fetch a record on maximum date of every month

oracle sql select max date for multiple records
sql select most recent date for each record
sql select most recent record by date
how to get latest record in sql by datetime
sql select row with max date
oracle select max date in where clause
sql max date
sql server max date value

I want to fetch customers balances at the maximum date of every month, in every year in database. The Balance table has balances at the end of everyday when customer does transaction. I just want to pick the balance at the maximum date of every month.Any help??

Below is a snip of My dataset.

You can do it also without a sub-query:

WITH b(ID, "date",bal) AS
  (
   SELECT 'CUST_I',DATE '2013-07-27', 14777.44 FROM dual UNION ALL
   SELECT 'CUST_H',DATE '2013-07-26', 71085.13 FROM dual UNION ALL
   SELECT 'CUST_I',DATE '2013-08-27', 66431.35656 FROM dual UNION ALL
   SELECT 'CUST_H',DATE '2013-08-26', 63102.68622 FROM dual UNION ALL
   SELECT 'CUST_H',DATE '2013-08-20', 6310.68622 FROM dual UNION ALL
   SELECT 'CUST_H',DATE '2013-08-10', 630.68622 FROM dual UNION ALL
   SELECT 'CUST_G',DATE '2013-09-25', 89732.04889 FROM dual UNION ALL
   SELECT 'CUST_E',DATE '2013-09-23', 83074.70822 FROM dual 
  )
SELECT ID,  
    MAX("date") KEEP (DENSE_RANK FIRST ORDER BY "date" desc) AS MAX_DATE,
    MAX(bal) KEEP (DENSE_RANK FIRST ORDER BY "date" desc) AS MAX_BAL
FROM b
GROUP BY ID, TRUNC("date", 'MM');

+-----------------------------+
|ID    |MAX_DATE  |MAX_BAL    |
+-----------------------------+
|CUST_E|23.09.2013|83074.70822|
|CUST_G|25.09.2013|89732.04889|
|CUST_H|26.07.2013|71085.13   |
|CUST_H|26.08.2013|63102.68622|
|CUST_I|27.07.2013|14777.44   |
|CUST_I|27.08.2013|66431.35656|
+-----------------------------+

SQL MAX() on date value, Here is a slide presentation of all aggregate functions. Practice SQL Exercises. SQL Exercises, Practice, Solution · SQL Retrieve data from tables [  I want to get last price for all products. But when I group with "product", I can't get a price from a row with maximum date. I can use MAX(), MIN() or COUNT() function in request, but I need a result based on other value. I want something like this in final: product price date 1 5 2014-03-28 2 30 2014-03-12 But I don't know how.

You can try using window function - row_number()

select * from
(
SELECT *,row_number() over(partition by extract(YEAR FROM Date), extract(MONTH FROM Date) order by date desc) as rn
FROM t
)rn=1

Solved: Month MAX record, Solved: Hi I need to filter the records based on Mnth MAX date avillable within the table I am having a table as below Date Department Salary. The query above will give you back the maximum transaction date for each month. To get the maximum Id associated with that date for each month, join the results of this back to the source table to get the max id for that day. So, your full query would look like this:

You may use a self join for your table call cust_balances :

select c1.*
  from cust_balances c1
join 
(  
  select max("date") max_date
    from cust_balances
   group by to_char("date",'yyyymm')  
) c2 on ( c1."date" = c2.max_date );

SQL Fiddle Demo

Trying to pull last day of month records from a daily records table., I've used a MAX(Date) subquery before and it functions. My expected result would be the last day of month records, not the last day on file's  Finally, date comparison can be tricky sometimes, since some values are stored as simple dates, others as date/times, so if you have this problem, you can normalize the table of last days of the month as strings (I needed to do that to make this scenario work for an Excel table)

Returning a result set with multiple rows based on max date , But I'm not sure how to get all three of the records I want. I'm not sure if this is a situation that calls for a sub-query or something else. Please note that the max date  31/01/2017 -> month 1 -> returns 31/01/2018 (maximum date for month 1 in the column of dates) I just changed a little and it's working perfectly for me: VAR currentMonth = MONTH( table[Date]) VAR currentYear = YEAR( table[Date] ) VAR latestDateofMonth = CALCULATE ( MAX ( table[Date] );

Find records with the most recent or oldest dates, If you want to return the records with the most recent or latest dates in a field, and The DatePart function extracts the month portion of the Birth Date field. you can design a top values query to retrieve three employee records, but the query  SQL MAX() on date value: Here we have discussed the usage of SQL MAX() function on date type of column of a table. SQL WHERE clause along with the SQL MAX() can be used as a subquery to find the maximum value of a column based upon some condition.

ENDOFMONTH function (DAX), Returns the last date of the month in the current context for the specified column of dates. Syntax. DAX Copy. ENDOFMONTH(  I want to fetch customers balances at the maximum date of every month, in every year in database. The Balance table has balances at the end of everyday when customer does transaction. I just want to pick the balance at the maximum date of every month.Any help?? Below is a snip of My dataset.

Comments
  • and the expected result for this data is ... missing
  • What about months with no data?
  • Should return null @SalmanA
  • Then you need a table of dates.