Solved

Sql query to last records from table

Posted on 2016-10-05
3
51 Views
Last Modified: 2016-10-08
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.
0
Comment
Question by:yadavdep
3 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 41830134
<Knee jerk reaction.  Write a subquery to get the most recent DateCreated for each CrewID, then join on the table as a main query.  Change YourTable and aliases to meet your needs. >

SELECT yt.CrewID, yt.Lat, yt.Lon, yt.DateCreated
FROM YourTable yt
   JOIN (
      SELECT CrewID, Max(DateCreated) as DateCreatedMax
      FROM YourTable
      GROUP BY CrewID) ytmax ON yt.CrewID = ytmax.CrewID AND yt.DateCreated = ytmax.DateCreatedMax
ORDER BY yt.CrewID

Open in new window

0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 41830605
SELECT CrewID, Lat, Lon, DateCreated
FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY CrewID ORDER BY DateCreated DESC) AS row_num
    FROM table_name
) AS derived
WHERE row_num = 1
0
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41830978
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

Open in new window

0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now