CODEWITHSUNDEEP
sqltimestamp
Kutti
Kutti

Reputation: 593

SQL last timestamp from set of data

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.

Upvotes: 0

Views: 97

Answers (4)

amdmax
amdmax

Reputation: 761

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

Upvotes: 0

Valerica
Valerica

Reputation: 1630

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.

Upvotes: 1

Niranjan Rajawat
Niranjan Rajawat

Reputation: 563

You can use group by as well:

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

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521093

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;

Upvotes: 0

Related Questions