Select Info from Table Where Row Has Max Date

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      

Select info from joined recordset where row has Max Date

A row-numbering solution should work.

This avoids the need to self-join:

SELECT Material, [Order], OrderDate
FROM (
    SELECT *,
        ROW_NUMBER() OVER
           (PARTITION BY POHD.Order ORDER BY POHD.OrderDate DESC) rn
    FROM POIT
    INNER JOIN POHD ON POHD.Order = POIT.Order
) p
WHERE rn = 1
ORDER BY POIT.Material

How to select max date from table for distinct values

You can do it by first selecting the max dates for each account and then forcing the match between accounts given the date constraints, like in the following query:

SELECT 
    *
FROM 
    (
    SELECT 
        MAX(date) AS date, 
        account
    FROM 
        tab 
    GROUP BY 
        account
    ) max_date_per_account
INNER JOIN 
    tab
ON 
    tab.date = max_date_per_account.date 
AND  
    tab.account = max_date_per_account.account

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

Selecting all columns, only from row with MAX(date), from joined table

This query should give you the results you want. It JOINs the files table to a list of latest event dates for each fileId (found by JOINing agendaItems and events) and then JOINs that to the agendaItems and events tables to get the appropriate event name:

SELECT f.*, a.Name
FROM files f
JOIN (SELECT fileId, MAX(e.date) AS date
      FROM agendaItems a
      JOIN events e ON e.id = a.eventId
      GROUP BY fileId) m ON m.fileId = f.id
JOIN (SELECT DISTINCT a.fileId, a.eventId, e.Name, e.date
      FROM agendaItems a 
      JOIN events e ON e.id = a.eventId) a ON a.fileId = f.id AND a.date = m.date

Output:

id  name            fileSize    Name
1   1 file name     1234786     Third Event
2   2 file name     6127833     Second Event
3   3 file name     1207834     Third Event
4   4 file name     2912873     Third Event
5   5 file name     7893465     Third Event

Demo on dbfiddle

How to select the min and max date from a table into another table on the same row? in SQL

You could try joining to grouped table:

select
    grouped.ID,
    grouped.AdmissionDate,
    grouped.DischargeDate,
    admission.Category admissionCategory,
    discharge.Category dischargeCategory
from 
(
    select
        ID,
        min(AdmissionDate) AdmissionDate,
        max(DischargeDate) DischargeDate
    from testTable
    group by ID
) [grouped]
left join testTable [admission] on
    [grouped].ID = [admission].ID and 
    [grouped].AdmissionDate = [admission].AdmissionDate
left join testTable [discharge] on
    [grouped].ID = [discharge].ID and 
    [grouped].DischargeDate = [discharge].DischargeDate

Database fiddle.

---

Related Topics