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
How to Convert Spark Schemardd into Rdd of My Case Class
How to Update in SQLite Using a Left Join to Select Candidate Rows
Retrieve the Maximum Length of a Varchar Column in SQL Server
Should I Set Max Pool Size in Database Connection String? What Happens If I Don'T
Database Design for 'Followers' and 'Followings'
How to Run .SQL File in Oracle SQL Developer Tool to Import Database
How to Run .SQL File in Oracle SQL Developer Tool to Import Database
MySQL Views - When to Use & When Not To
Select Something That Has More/Less Than X Character
T-SQL Query:Getting Child Nodes of a Parent
Postgresql Syntax Check Without Running the Query
Delete Rows with Foreign Key in Postgresql