Fetch a Record on Maximum Date of Every Month

How to select MAX date of each month in database

SELECT max([date_field]) as date,
FROM [database].[dbo].[Table]
GROUP BY month([date])
ORDER BY month(date) ASC;

Selecting max date of each month

February is not over yet. Perhaps a window function does what you want:

SELECT yyyy_mm_dd, id, name, cumulative_metric1, cumulative_metric2
FROM (SELECT t.*,
MAX(yyyy_mm_dd) OVER (PARTITION BY last_day(yyyy_mm_dd)) as last_yyyy_mm_dd
FROM mytable t
) t
WHERE yyyy_mm_dd = last_yyyy_mm_dd;

This calculates the last day in the data.

sql select everything with maximum date (that that is smaller than a specific date) without subqueries

You can find the maximum date without sub-queries.

SELECT t.*,
max("DATE") OVER(PARTITION BY id ORDER BY "DATE" DESC) as max_date
FROM "TABLE" t
WHERE "DATE" < DATE '2021-07-16'

You need a sub-query to filter to only show the row(s) with the maximum date:

SELECT *
FROM (
SELECT t.*,
max("DATE") OVER(PARTITION BY id ORDER BY "DATE" DESC) as max_date
FROM "TABLE" t
WHERE "DATE" < DATE '2021-07-16'
)
WHERE "DATE" = max_date;

However, you are still only querying the table once using this technique even though it uses a sub-query.

Note DATE and TABLE are reserved words and cannot be used as unquoted identifiers; it would be better practice to use different names for those identifiers.

You could, equivalently use the RANK or DENSE_RANK analytic functions instead of MAX; ROW_NUMBER, however, does not give the same output as it will only return a single row and will not return all tied rows.

SELECT *
FROM (
SELECT t.*,
RANK() OVER(PARTITION BY id ORDER BY "DATE" DESC) as rnk
FROM "TABLE" t
WHERE "DATE" < DATE '2021-07-16'
)
WHERE rnk = 1;

But you still need a sub-query to filter the rows.


If you want to not use a sub-query then you can use:

SELECT id,
MAX("DATE") AS "DATE",
MAX(col1) KEEP (DENSE_RANK LAST ORDER BY "DATE", ROWNUM) AS col1,
MAX(col2) KEEP (DENSE_RANK LAST ORDER BY "DATE", ROWNUM) AS col2,
MAX(col3) KEEP (DENSE_RANK LAST ORDER BY "DATE", ROWNUM) AS col3
FROM "TABLE"
GROUP BY id

However, that is not quite the same as it will only get a single row per id and will not return multiple rows tied for the greatest date per id.

Select info from table where row has max date

SELECT group,MAX(date) as max_date
FROM table
WHERE checks>0
GROUP BY group

That works to get the max date..join it back to your data to get the other columns:

Select group,max_date,checks
from table t
inner join
(SELECT group,MAX(date) as max_date
FROM table
WHERE checks>0
GROUP BY group)a
on a.group = t.group and a.max_date = date

Inner join functions as the filter to get the max record only.

FYI, your column names are horrid, don't use reserved words for columns (group, date, table).

SQL Server: SELECT only the rows with MAX(DATE)

If rownumber() over(...) is available for you ....

select OrderNO,
PartCode,
Quantity
from (select OrderNO,
PartCode,
Quantity,
row_number() over(partition by OrderNO order by DateEntered desc) as rn
from YourTable) as T
where rn = 1

SQL Get max date in dataset for each month

CREATE TABLE #foo (id INT, d DATETIME);

INSERT #foo(id,d) SELECT 1, '20091101'
UNION ALL SELECT 2, '20091102'
UNION ALL SELECT 3, '20091006'
UNION ALL SELECT 4, '20091001'
UNION ALL SELECT 5, '20091002'
UNION ALL SELECT 6, '20090904';

SELECT d, id
FROM
(
SELECT d, id, rn = ROW_NUMBER() OVER
(PARTITION BY DATEDIFF(MONTH, '20000101', d)
ORDER BY d DESC)
FROM #foo
) AS x
WHERE x.rn = 1
ORDER BY x.d;

DROP TABLE #foo;


Related Topics



Leave a reply



Submit