Sql query to last records from table
I have sql table with these columns
CrewID
Lat
Lon
DateCreated
Now this table stores Latitude and Longitude of each crew when he travels along with Datetime when the position is recorded.
I need to write a query which will give me the last recorded position of each crew from this table bases on DateCreated.
CrewID
Lat
Lon
DateCreated
Now this table stores Latitude and Longitude of each crew when he travels along with Datetime when the position is recorded.
I need to write a query which will give me the last recorded position of each crew from this table bases on DateCreated.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SELECT x.CrewID, x.Lat, x.Lon, n.DateCreated
FROM YourTable x
CROSS APPLY
(
SELECT MAX(y.DateCreated) DateCreated
FROM YourTable y
WHERE x.CrewID = y.CrewID
)n
FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY CrewID ORDER BY DateCreated DESC) AS row_num
FROM table_name
) AS derived
WHERE row_num = 1