Group by With Max(Date)

GROUP BY with MAX(DATE)

You cannot include non-aggregated columns in your result set which are not grouped. If a train has only one destination, then just add the destination column to your group by clause, otherwise you need to rethink your query.

Try:

SELECT t.Train, t.Dest, r.MaxTime
FROM (
      SELECT Train, MAX(Time) as MaxTime
      FROM TrainTable
      GROUP BY Train
) r
INNER JOIN TrainTable t
ON t.Train = r.Train AND t.Time = r.MaxTime

Group by max date and id

Something like this should work:

SELECT t1.id, t1.value, t1.date
FROM your_table t1
INNER JOIN (
    SELECT id, MAX(date) date
    FROM your_table
    GROUP BY id
) t2
ON t1.id = t2.id AND t1.date = t2.date

GROUP BY having MAX date

Putting the subquery in the WHERE clause and restricting it to n.control_number means it runs the subquery many times. This is called a correlated subquery, and it's often a performance killer.

It's better to run the subquery once, in the FROM clause, to get the max date per control number.

SELECT n.* 
FROM tblpm n 
INNER JOIN (
  SELECT control_number, MAX(date_updated) AS date_updated
  FROM tblpm GROUP BY control_number
) AS max USING (control_number, date_updated);

return rows of sql query with MAX(date) and GROUP BY id

SELECT t.id, t.position, r.Maxdate
FROM (
      SELECT id, MAX(date) as Maxdate
      FROM Yourtable
      GROUP BY id
) r
INNER JOIN Yourtable t
ON t.id = r.id AND t.date = r.Maxdate

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

LINQ max date with join and group

If you stop for a second and write same query in sql you will come the same realization that amount cannot be selected from those groups just because you have multiple values within your store&device group. As pointed by @Juharr you can make an aggregation over amount as most likely it makes sens that you want to know sum of those amounts rather than one random of them from the group? or maybe you know which one you need? the one with max date?

if the one with max date you are after than you need to join device after the group by and select it:

var query = (from device in db.DeviceList
        join store in db.stores on device.storeID equals store.id
       group device by new { device.storeID, device.deviceID } into g
        select new
        {
           
            deviceID = g.Key.deviceID,
            storeID = g.Key.storeID,
            MaxDate = g.Max(d => d.Date)
        }) into s
        let dev = db.devices.FirstOrDefault(x => 
         x.deviceID == s.deviceID 
         && x.storeID == s.storeID 
         && s.MaxDate == x.Date)
        join type in db.devices on dev.deviceID equals type.id
        select new {
            s.deviceID,
            s.storeID,
            s.MaxDate,
            dev.amount,
            type.name
         };

this is also not perfect as you can have multiple device records with the same date that happens to be max so it will choose one at (semi) random or you can add some order before that FirstOrDefault

Short answer is you did not define properly what you want to read from the database in regards of that amount or type.

---

Also as a pro tip do not use join syntax until you have to. Writing queries is this manner by default reduces EF capabilities to generate queries for you, increases your workload as you need to think about relations and foreign keys and not on what the requirements are. You should use Navigation Properites by default and when EF fails to create proper code you can fall back to join syntax and fix the query. More often than not EF query is good enough.

Group by ID and keep latest date

You can use an if_else statement to check for latest date, and replace any date that is not the latest.

library(dplyr)

df %>% 
  group_by(ID) %>% 
  mutate(date = if_else(date == max(date), as.Date(date), as.Date(max(date))))

# A tibble: 6 × 2
# Groups:   ID [3]
     ID date      
  <int> <date>    
1     1 2020-06-13
2     1 2020-06-13
3     2 2021-01-02
4     2 2021-01-02
5     3 2022-01-07
6     3 2022-01-07

---

Related Topics