CODEWITHSUNDEEP
mysqlsqldatabase
esjeidee
esjeidee

Reputation: 11

How to select the last row of a table in sql?

I have columns from different tables which were joined by their common keys. What I want to do is to select the very last data of each rows with the same ev_k. Kindly refer to the table below.

3489    7666    LOND                                c   i   N   10/21/2013  09:30:29
3490    7666    LOND                                c   c   N   10/21/2013  09:30:29
3491    7666    LOND                                l   i   N   10/21/2013  09:30:29
3492    7666    LOND                                l   d   N   10/21/2013  09:30:29
3493    7666    LOND                                l   c   N   10/21/2013  09:32:52
3494    7666    LOND                                i   i   N   10/21/2013
3495    7666    LOND                                i   d   N   10/21/2013  09:32:52
3496    7666    LOND                                i   p   N   10/21/2013  09:51:20
2159    6367    LOND                                c   i   N   10/21/2013  10:25:00
2160    6367    LOND                                c   c   N   10/21/2013  10:25:00
2162    6367    LOND                                l   d   N   10/21/2013  10:25:00
2161    6367    LOND                                l   i   N   10/21/2013  10:25:00
2163    6367    LOND                                l   p   N   10/21/2013  10:25:08
3371    7463    LOND                                c   i   N   10/21/2013  11:07:23
3374    7463    LOND                                l   d   N   10/21/2013  11:07:24
3373    7463    LOND                                l   i   N   10/21/2013  11:07:24
3372    7463    LOND                                c   c   N   10/21/2013  11:07:24
3377    7463    LOND                                i   d   N   10/21/2013  11:07:32
3376    7463    LOND                                i   i   N   10/21/2013  11:07:32
3375    7463    LOND                                l   c   N   10/21/2013  11:07:32
3378    7463    LOND                                i   c   N   10/21/2013  11:07:41
3380    7463    LOND                                c2  c   N   10/21/2013  11:07:42
3381    7463    LOND                                a1  i   N   10/21/2013  11:07:42
3382    7463    LOND                                a1  d   N   10/21/2013  11:07:42
3379    7463    LOND                                c2  i   N   10/21/2013  11:07:42
3383    7463    LOND                                a1  c   N   10/21/2013  11:07:51
3384    7463    LOND                                r   i   N   10/21/2013  11:07:51
3385    7463    LOND                                r   c   N   10/21/2013  11:07:52
3387    7463    LOND                                -   c   Y   10/21/2013  11:07:53
3386    7463    LOND                                -   i   N   10/21/2013  11:07:53

In the table provided, what I want to retrieve are the following rows:

3496    7666    LOND                                i   p   N   10/21/2013  09:51:20
2163    6367    LOND                                l   p   N   10/21/2013  10:25:08
3386    7463    LOND                                -   i   N   10/21/2013  11:07:53

The script used to get the data on the table was written below:

SELECT T1.KEY, T1.ev_k, T1.BRANCH, T2.THEIR_REF, T1.TYPE, T1.STATUS, T1.ISLASTSTEP, T1.FINISHDATE, T1.FINISHTIME 
FROM STEPHIST T1, BASEEVENT T2, EXEMPL30 T3
WHERE T1.EVENT_KEY = T2.KEY97
AND T2.EXEMPLAR = T3.KEY97
ORDER BY THEIR_REF, FINISHTIME;

Thank you.

Upvotes: 0

Views: 65

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269483

You should learn to use proper join syntax and to use table aliases that are abbreviations for the tables they refer to.

Then, you just need to calculate and join in the maximum time:

SELECT s.KEY, s.ev_k, s.BRANCH, b.THEIR_REF, s.TYPE, s.STATUS,
       s.ISLASTSTEP, s.FINISHDATE, s.FINISHTIME 
FROM STEPHIST s JOIN
     BASEEVENT b
     ON s.EVENT_KEY = b.KEY97 JOIN
     EXEMPL30 e
     ON b.EXEMPLAR = e.KEY97 JOIN
     (SELECT s.ev_k, MAX(FINISHTIME) as maxft
      FROM STEPHIST s
      GROUP BY s.ev_k
     ) ss
     ON s.ev_k = ss.ev_k AND s.finishtime = ss.msxft
ORDER BY Tb.HEIR_REF, s.FINISHTIME;

Upvotes: 1

Related Questions