Select Only Rows With Max Date

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



Leave a reply



Submit