SQL Select Max(Date) and Corresponding Value

SQL select max(date) and corresponding value

You can use a subquery. The subquery will get the Max(CompletedDate). You then take this value and join on your table again to retrieve the note associate with that date:

select ET1.TrainingID,
  ET1.CompletedDate,
  ET1.Notes
from HR_EmployeeTrainings ET1
inner join
(
  select Max(CompletedDate) CompletedDate, TrainingID
  from HR_EmployeeTrainings
  --where AvantiRecID IS NULL OR AvantiRecID = @avantiRecID
  group by TrainingID
) ET2
  on ET1.TrainingID = ET2.TrainingID
  and ET1.CompletedDate = ET2.CompletedDate
where ET1.AvantiRecID IS NULL OR ET1.AvantiRecID = @avantiRecID

Select Value by Max Date

Your relational design might be improved. It's an anti-pattern to mix different types of data in the same column.

While stuck with the given setup, two subqueries with DISTINCT ON and a FULL OUTER JOIN do the job:

SELECT customer_id, p.value AS price, c.value AS condition
FROM  (
   SELECT DISTINCT ON (customer_id)
          customer_id, value
   FROM   tbl
   WHERE  item = 'condition'
   ORDER  BY customer_id, timestamp DESC
   ) c
FULL JOIN (
   SELECT DISTINCT ON (customer_id)
          customer_id, value
   FROM   tbl
   WHERE  item = 'price'
   ORDER  BY customer_id, timestamp DESC
   ) p USING (customer_id)

db<>fiddle here

See:

• Select first row in each GROUP BY group?

This assumes timestamp to be defined NOT NULL, or you'll want NULLS LAST.

Depending on undisclosed cardinalities and value distribution, there may be (much) faster query variants.

If there is a customer table with distinct customer_id, (much) faster query styles become possible.

These partial, multicolumn indexes would be perfect to make it fast in any case:

CREATE INDEX tbl_condition_special_idx ON tbl (customer_id, timestamp DESC, value) WHERE item = 'condition';
CREATE INDEX tbl_price_special_idx     ON tbl (customer_id, timestamp DESC, value) WHERE item = 'price';

See:

• Optimize GROUP BY query to retrieve latest row per user

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      

Get max value and max date from sql query

try removing CallDate, CallID from the group by clause.

So :

SELECT MemberID, FirstName, LastName, MAX(CallDate) as CallDate, MAX(CallID) as CallID

FROM dbo.table 
GROUP BY MemberID, FirstName, LastName 

ORDER BY LastName asc;

Hopefully that should do it.

How to select values that correspond with a max date in a mysql query

You should try this Query also see the screenshot for verification (see image 1)

SELECT r.`contract_id`,r.`start_date`,
(SELECT sub_r.`end_date` FROM `table` sub_r where sub_r.`contract_id` = r.`contract_id` 
ORDER BY sub_r.`revenue` DESC limit 1 )
AS `end_date`,
(SELECT sub_r.`revenue` FROM `table` sub_r where sub_r.`contract_id` = r.`contract_id` 
ORDER BY sub_r.`revenue` DESC limit 1 )
AS `revenue` 
FROM `table` r GROUP BY r.`contract_id`;

Image 1

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).

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

---

Related Topics