Return Value at Max Date for a Particular Id

Return value at max date for a particular id

You can use the following:

select t1.id, t2.mxdate, t1.value
from yourtable t1
inner join
(
  select max(date) mxdate, id
  from yourtable
  group by id
) t2
  on t1.id = t2.id
  and t1.date = t2.mxdate

See Demo

select rows in sql with latest date for each ID repeated multiple times

This question has been asked before. Please see this question.

Using the accepted answer and adapting it to your problem you get:

SELECT tt.*
FROM myTable tt
INNER JOIN
    (SELECT ID, MAX(Date) AS MaxDateTime
    FROM myTable
    GROUP BY ID) groupedtt 
ON tt.ID = groupedtt.ID 
AND tt.Date = groupedtt.MaxDateTime

Selecting Max Date Per Day For Selected ID

• In your Sub-select query (Derived Table), you will need to group by on the date as well, using Date() function. This will give you max date value per day and id.

Try the following:

 SELECT a.*
 FROM turtle_locations AS a
 INNER JOIN
    (SELECT turtle_id, 
            DATE(`date`) AS day_date, 
            MAX(`date`) AS maxdate
    FROM turtle_locations 
    GROUP BY turtle_id, day_date) AS groupedtt 
 ON a.turtle_id = groupedtt.turtle_id 
 AND a.`date` = groupedtt.maxdate 

DB Fiddle Demo

Retrieve max date for distinct IDs in a table

One way is to use RANK:

WITH cte AS (
  SELECT ABC.*, RANK() OVER(PARTITION BY Id,Name ORDER BY Date DESC) rnk
  FROM ABC 
)
SELECT *
FROM cte
WHERE rnk = 1
ORDER BY id;

How to get max date among others ids for current id using BigQuery?

Your version is good enough I think. But if you want to try other options - consider below approach. It might looks more verbose from first look - but should be more optimal and cheaper to compare with your version with cross join

temp as (
  select id, 
    greatest(
      ifnull(max(max_date_for_id) over preceding_ids, '1970-01-01'), 
      ifnull(max(max_date_for_id) over following_ids, '1970-01-01')
    ) as max_date_for_rest_ids
  from (
    select id, max(rep_date) max_date_for_id
    from t
    group by id
  )
  window 
    preceding_ids as (order by id rows between unbounded preceding and 1 preceding),
    following_ids as (order by id rows between 1 following and unbounded following)
)
select *
from t 
join temp
using (id)    

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.

Get max date based on another column in SQL

Is this what you want?

SELECT *
FROM   (
           SELECT *, RN = ROW_NUMBER() OVER (ORDER BY dates DESC)
           FROM   @DateValues
           WHERE  status = 'ABC'           
       ) AS D
WHERE  D.RN = 1

---

Related Topics