I have a SQL query:
x
SELECT number, ResponseTime, TicketCreateTime,
round(time_to_sec(timediff(ResponseTime, TicketCreateTime))/60,2) AS FRMins
FROM (SELECT TE.id, T.number, T.ticket_id, TE.thread_id, TE.pid, T.created AS TicketCreateTime, TE.created AS ResponseTime, TE.type, TE.staff_id
FROM ost_ticket T INNER JOIN
ost_thread_entry TE
ON T.ticket_id = TE.thread_id
WHERE TE.type = 'N' OR
TE.type = 'R' AND TE.id IN (SELECT min(id) FROM ost_thread_entry WHERE type = 'N' OR type = 'R' GROUP BY thread_id)
) AS FTRT_tbl;
number responseTime TicketCreateTime FRMins
120985 2019-09-02 14:28:00 2019-09-02 14:10:00 18.0
120985 2019-09-02 14:32:00 2019-09-02 14:10:00 22.0
123490 2019-11-03 16:18:00 2019-11-03 16:17:00 1.0
123490 2019-11-03 17:18:00 2019-11-03 16:17:00 61.0
But my goal is to have only one of the number and it should be this one with the lowest FRMins. I tried it in many different ways, but I don’t get it right.
It should look like this one
number responseTime TicketCreateTime FRMins
120985 2019-09-02 14:28:00 2019-09-02 14:10:00 18.0
123490 2019-11-03 16:18:00 2019-11-03 16:17:00 1.0
Advertisement
Answer
You can use WHERE EXISTS (SELECT ... GROUP BY ... HAVING)
subquery to get the distincts number
s having the lowest FRMins
:
Schema (MySQL v5.7)
CREATE TABLE test (
`number` INTEGER,
`responseTime` DATETIME,
`TicketCreateTime` DATETIME,
`FRMins` INTEGER
);
INSERT INTO test
(`number`, `responseTime`, `TicketCreateTime`, `FRMins`)
VALUES
(120985, '2019-09-02 14:28:00', '2019-09-02 14:10:00', 18.0),
(120985, '2019-09-02 14:32:00', '2019-09-02 14:10:00', 22.0),
(123490, '2019-11-03 16:18:00', '2019-11-03 16:17:00', 1.0),
(123490, '2019-11-03 17:18:00', '2019-11-03 16:17:00', 61.0);
Query #1
SELECT *
FROM test
WHERE EXISTS
(
SELECT 1
FROM test t
GROUP BY t.number
HAVING FRMins = MIN(t.FRMins)
);
Output :
| number | responseTime | TicketCreateTime | FRMins |
| ------ | ------------------- | ------------------- | ------ |
| 120985 | 2019-09-02 14:28:00 | 2019-09-02 14:10:00 | 18 |
| 123490 | 2019-11-03 16:18:00 | 2019-11-03 16:17:00 | 1 |