How can I write my SQL query in LINQ?

Question

I have a table that looks like this. The query needs to return the last date entry for each location. How would I write this in SQL and LINQ?

Id  Location        Salary  Date
1   Los Angeles     $1500   Jan-05-1999 
2   San Diego       $250    Jan-07-1999 
3   Los Angeles     $300    Jan-08-1999
4   Boston          $700    Jan-08-1999
5   Los Angeles     $300    Jan-08-1999

The result would be:

2   San Diego       $250    Jan-07-1999
4   Boston          $700    Jan-08-1999
5   Los Angeles     $300    Jan-08-1999

Answer 1

SELECT location, max(date)

FROM yourtable

GROUP BY location

EDIT: I didn't see the results being asked for or not sure if it was there originally:

SELECT yourtable.id, yourtable.location, yourtable.salary, yourtable.date
FROM yourtable INNER JOIN
  (SELECT yourtable.location, MAX(yourtable.date) as thedate
   FROM yourtable
   GROUP BY yourtable.location) AS MYRESULT 
   on MYRESULT.thedate = yourtable.date  
      AND MYRESULT.location = yourtable.location

Haven't tested this though.

Answer 2

Keep in mind I'm pretty new to link, and I don't think this is the best way:

var collection = from d in <TABLE> select d;
collection = collection.GroupBy(x => x.Date).Select(x => x.Last());

Basically just group on the date and take the last one. I couldn't think of a good way to do this in the LINQ command, so I just selected all the rows - anyone know to combine these lines?

Answer 3

@Bobby Shaftoe

I believe that he was looking for the latest entry rather than the latest date for every location. If my assumption is correct, it would be more like

SELECT max(ID),location,date
FROM tablename
GROUP BY location

Answer 4

To get the whole row with the max date for each location, I think the SQL is:

SELECT t1.Id, t1.Location, t1.Salary, t1.Date
FROM YourTable t1, 
    (SELECT Location, MAX(Date) AS MaxDate
    FROM YourTable
    GROUP BY Location) t2
WHERE t1.Location = t2.Location
AND t1.Date = t2.MaxDate

Please correct me if I'm wrong!

Answer 5

Actually, this is not so straight forward if I read this question correctly. Because Los Angeles has 2 entries on the Max date there appears to be a further rule that in that situation the max ID should be selected.

In that case the SQL becomes a little more complex, 2 groupings are required....

select id, location, salary, date from 
locations where id in
(select  max(id) as MaxID
from locations where date in (
Select Max(Date) as MaxDate from Locations group by Location)
Group by location)

The corresponding Linq query, would also require 2 groupings...

  var qry = from a in locations
            group a by a.Location into grp
            from b in grp
            where b.Date == grp.Max(o => o.Date)
            group b by b.Location into grp2
            from c in grp2
            where c.ID == grp2.Max(obj => obj.ID)
            orderby c.ID
            select c;

Answer 6

If your dates included time, then you could:

var locations = 
   from l in mycontext.Locations
   where l.Date = mycontext.Locations
         .Where(l2=>l.Location == l2.Location)
         .Max(l2.Date)
   select l;

Alternatively, assuming ID is an identity and the records are inserted in the table when they occurred(your example seems to hint so), you can do:

var locations = 
   from l in mycontext.Locations
   where l.ID = mycontext.Locations
         .Where(l2=>l.Location == l2.Location)
         .Max(l2.ID)
   select l;