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
Select only rows with max date
Your query returns what you need - only one row for each _id where column _status_set_at has its max value.
You do not need to change anything in your original query.
count(_id) shows how many rows for each _id in the original table, but not in a query result.
Query result has only one row for each _id because you group by _id.
This query shows that in your query result there is only one row for each _id
SELECT _id, max_status_set_at, count(_id) FROM (
SELECT _id, max(_status_set_at) max_status_set_at
FROM pikta.candidates_states
GROUP BY _id) t
GROUP BY _id
If you need apply a condition on max(_status_set_at) you can use HAVING
SQL: SELECT rows with max date from created table
Many databases support LEAST()
/GREATEST()
. Perhaps the most efficient method assuming there are no duplicates is:
WITH i AS (
SELECT sat.INVOICEDATE, sat.SERIALID, sat.QTY
FROM SALESTRANS sat
UNION ALL
SELECT set.INVOICEDATE, set.SERIALID, set.QTY
FROM SERVICETRANS sat
)
SELECT i.*
FROM (SELECT i.*,
ROW_NUMBER() OVER (PARTITION BY SERIALID ORDER BY INVOICEDATE DESC) as seqnum
FROM i
) i
WHERE seqnum = 1;
Note that if there are two invoices on the same date, this arbitrarily returns one of them. Use RANK()
if you want both.
Note that this uses UNION ALL
, not UNION
. UNION
incurs overhead for removing duplicates. That does not seem useful for a query like this.
Depending on your database (and data), there are possibly alternative ways of expressing this query, some might have better performance characteristics.
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 select one row from max(date)
You can try to use ROW_NUMBER
with window function to make the row number by idStyle
, idStyleDtl
, idCustomer
columns and order by createDt
DESC in subquery.
then get rn = 1
data, which mean the max date.
SELECT * FROM (
SELECT
idStyle,
idStyleDtl,
costPrice,
createDt,
idCustomer,
ROW_NUMBER() OVER(PARTITION BY idStyle, idStyleDtl, idCustomer ORDER BY createDt DESC) rn
FROM INVOICE_DTL
) t1
where rn = 1
ORDER BY
idStyle, idCustomer
Related Topics
How to Get Textual Contents from Blob in Oracle SQL
Inserting Date Value into Date Field Using Laravel
How to Select the Last Record from MySQL Table Using SQL Syntax
Two Rows With the Same Id and Two Different Values, Getting the Second Value into Another Column
How to Display Half Records from Table - MySQL
Sql String: Counting Words Inside a String
Using Nullif to Divide by Zero
Return Dummy Value If No Row Present for in Clause
Mysql Left Join Not Returning Null for Missing Rows
Update Table Column With Different Random Numbers
Sql Query to Get the Employee Name and Their Manager Name from the Same Table
How to Count Number of Digits After a Decimal Place
Mysql Convert Date from Mm-Dd-Yyyy to Yyyy-Mm-Dd Format
Left Join Without Duplicate Rows from Left Table
Convert Varchar Mmddyyyy to Mm/Dd/Yyyy Datetime and Select the Most Recent Date Only
How to View SQL Table Structure in Oracle SQL Developer
How to Subtract One Day from Current Date Then Convert to String in Hive
How to Connect MySQL Workbench to Running MySQL Inside Docker