-2

I have table, like:

Id Account Date Amount
1. ad@ind 07.11.2022 354
2. ad@ind 10.11.2022 586

I need to find record, where is maximum number of month and get amount in this date. How is it to do? Max(date) does'nt fit, i need number inside month.

I tried to do group by date and amount, i tried to do inner query, but it was'nt lucky.

Isolated
  • 5,169
  • 1
  • 6
  • 18
danilchik22
  • 15
  • 2
  • This is going to look something like `SELECT * FROM yourtable WHERE date = (SELECT max(date) FROM yourtable)`. – JNevill Jun 19 '23 at 14:38
  • Not sure if I'm understanding the question, but you might be interested in combining `extract(month from date)` into JNevill's answer. – EdmCoff Jun 19 '23 at 14:41
  • Oh. "Number inside month" "maximum number of month" That was lost in translation for me @EdmCoff. Good catch. – JNevill Jun 19 '23 at 14:43
  • Sorry, what would be the month for `07.11.2022`? 7 or 11? What if one month is in 2022 and the other in 2023? What if there is data for two accounts? – Salman A Jun 19 '23 at 14:55
  • 1
    Also note that tables have _rows_, not records. – jarlh Jun 19 '23 at 16:37

1 Answers1

1

Assuming you want a record for each month, then consider the following solution using window function ROW_NUMBER():

SELECT id, account, date, amount
FROM 
    (
        SELECT yt.*,
           ROW_NUMBER() OVER (PARTITION BY EXTRACT(YEAR FROM date), EXTRACT(MONTH FROM date) ORDER BY date DESC) as rn
        FROM yourtable yt 
     )dt
WHERE rn = 1;

This ROW_NUMBER() window function feature is generating a row_number, starting at 1, for each row that shares the same month and year, ordering by the date column descending. The record that gets rn of 1 is the highest date for that month/year combination.

Using similar logic as I shared in the comment, which is likely slower since two table scans are needed:

SELECT *
FROM yourtable yt
WHERE date = 
    (
        SELECT max(date)
        FROM yourtable yt2
        WHERE
           EXTRACT(MONTH FROM yt.date) = EXTRACT(MONTH FROM yt2.date
           AND EXTRACT(YEAR FROM yt.date) = EXTRACT YEAR FROM yt2.date)
     )
JNevill
  • 46,980
  • 4
  • 38
  • 63