CODEWITHSUNDEEP
sqloracle-databasetimestampmax
Leafer
Leafer

Reputation: 15

Most recent TIMESTAMP joining the same table

I have a table with columns: START POINT, END POINT, START DATE, END DATE,RIDE NUMBER I need to receive all records from it, and END POINT, STARTE DAT, END DATE for most recent record (start date) which is from the same start point.

Example:

  1. Start point - A, end point - B, start date 1/1/1 10:30, end date 1/1/1 10:35
  2. Start point - A, end point - C, start date 1/1/1 10:20, end date 1/1/1 10:35
  3. Start point - A, end point - B, start date 1/1/1 10:25, end date 1/1/1 10:35
  4. Start point - A, end point - D, start date 1/1/1 10:27, end date 1/1/1 10:35

So I need to receive last ride (START DATE, end date does not matter ) from the same START POINTt (in the example it's A, end point does not matter) for every record.

So for ride 1 I should get ride 4. For 2 I should get nothing (it's the last ride), for 3 I should get record 2 and for record 4 I should get record 3. And so on.

The problem is there is many start points, and many start dates.

I was trying to join same table with start dates condition using MAX function (that start date from first trip < maX(start date from last trip), but it's not working.

Do you have an idea how to join it?

Upvotes: 0

Views: 115

Answers (1)

VN&#39;sCorner
VN&#39;sCorner

Reputation: 1552

Something like below should do the job. However if there are more than one records for the same startedate both records will be returned. You might have to decide to pick either one by picking min rowid or max rowid.

 Select 
    a.STARTPOINT,a.ENDPOINT, a.STARTDATE, a.ENDDATE,a.RIDENUMBER,recentride.ENDPOINT,recentride.STARTEDATE,recentride.ENDDATE
    from ridetable a,ridetable recentride
    where a.startpoint = recentride.startpoint 
    and  recentride.startedate < a.STARTEDATE
    and  recentride.startedate = (select max(c.STARTEDATE) 
                                  from ridetable c
                                  where c.startpoint = a.startpoint
                                  and c.startedate < a.STARTEDATE)

Upvotes: 1

Related Questions