SQL last timestamp from set of data

Question

I have a problem with getting a last timestamp for each nr in my db.

+--------+---------------------+------------------------------+
|  nr    |        type         |              ts              |
+--------+---------------------+------------------------------+
|        |                     |                              |
|  1002  |          RSC        |  2017-12-05 07:53:42         |
|  1002  |          RSC        |  2017-12-05 07:52:42         |
|  1004  |          RSC        |  2017-12-05 07:54:42         |
|  1004  |          RSC        |  2017-12-05 07:51:42         |
|  1004  |          RSC        |  2017-12-05 07:51:42         |
+--------+---------------------+------------------------------+

Result after select:

+-------+---------------------+-------------------------+
| 1002  |           RSC       |  2017-12-05 07:53:42    |
+-------+---------------------+-------------------------+
| 1004  |          RSC        |  2017-12-05 07:54:42    |
+-------+---------------------+-------------------------+

Thank you for help.

Just use `GROUP BY` and `MAX` – Valerica Dec 05 '17 at 11:42 — Valerica, Dec 05 '17 at 11:42

score 1 · Answer 1 · answered Dec 05 '17 at 11:43

1

You can use group by as well:

SELECT nr, type, MAX(ts) FROM TABLE
group by nr, type

answered Dec 05 '17 at 11:43

Niranjan Rajawat

553
2
9

score 1 · Answer 2 · answered Dec 05 '17 at 11:44

Here is a solution as I suggested in comment:

SELECT
    nr
    ,type
    ,MAX(ts) AS [Last Timestamp]
FROM <your_table>
GROUP BY nr, type

Summarizing Values: GROUP BY Clause and Aggregate Functions this link will help you understand better the GROUP BY clause and Aggregate Functions.

score 0 · Answer 3 · answered Dec 05 '17 at 11:43

You can use ROW_NUMBER here:

SELECT nr, type, ts
FROM
(
    SELECT nr, type, ts,
        ROW_NUMBER() OVER (PARTITION BY nr ORDER BY ts DESC) rn
    FROM yourTable
) t
WHERE t.rn = 1;

If your version of SQL does not have analytic functions, then we can also join to a subquery to get the same result:

SELECT t1.*
FROM yourTable t1
INNER JOIN
(
    SELECT nr, MAX(ts) AS max_ts
    FROM yourTable
    GROUP BY nr
) t2
    ON t1.nr = t2.nr AND
       t1.ts = t2.max_ts;

amdmax · Answer 4 · 2017-12-05T11:49:23.830

0

in a sense you need to group by nr and then use min or max on the timestamp if the timestamp is determining the order. otherwise you're looking into something more complex like in this answer: Select first row in each GROUP BY group? The actual syntax varies a bit depending on the database but the first answer is about right in plain ANSI SQL

SELECT nr, type, MAX(ts) FROM t
GROUP BY nr, type

edited Dec 05 '17 at 11:49

answered Dec 05 '17 at 11:44

amdmax

771
3
14

SQL last timestamp from set of data

4 Answers4