CODEWITHSUNDEEP
mysqlsql
harp1814
harp1814

Reputation: 1658

Select only most recent record

There is a MariaDB table

calldate timestamp,
channel  varchar(30)

Values are:

2019-12-01 SIP/91953838-084ec34
2019-12-02 SIP/91953838-023ec35
2019-12-03 SIP/91953839-03e4523
2019-12-04 SIP/91953839-0365hee

How to select only most recent record for each channel?

So i need result as:

2019-12-02 SIP/91953838
2019-12-04 SIP/91953839

'91953838' or '91953839' are actually channel numbers (about 50 unique values) whereas '084ec34' just system code assigned to each calls randomly so there is no two records like that:

    2019-12-01 SIP/91953838-084ec34
    2019-12-02 SIP/91953838-084ec34

Table contains 10+ millions rows, so i need most effective query. Mysql Ver 15.1 Distrib 5.5.60-MariaDB,

Upvotes: 2

Views: 84

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522712

Using ROW_NUMBER we can try:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY SUBSTRING_INDEX(channel, '-', 1) ORDER BY timestamp DESC) rn
    FROM yourTable
)

SELECT timestamp, channel
FROM cte
WHERE rn = 1;

If your version of MySQL/MariaDB does not support ROW_NUMBER, then we have to do more work:

SELECT t1.timestamp, t1.channel
FROM yourTable t1
INNER JOIN
(
    SELECT SUBSTRING_INDEX(channel, '-', 1) AS channel, MAX(timestamp) AS max_timestamp
    FROM yourTable
    GROUP BY SUBSTRING_INDEX(channel, '-', 1)
) t2
    ON SUBSTRING_INDEX(t1.channel, '-', 1) = t2.channel AND
       t1.timestamp = t2.max_timestamp;

Upvotes: 3

Related Questions