CODEWITHSUNDEEP
mysqlsql
ayamgoreng
ayamgoreng

Reputation: 113

select the second longest time from mysql query

I want to know the second longest time by topic using the sql query below but it resulted in error:

SELECT h.topic AS Help_Topic, MAX(TIMESTAMPDIFF( MINUTE , t.created, t.closed )) as SecondMax
FROM ost_ticket t, ost_help_topic h
WHERE t.topic_id = h.topic_id
AND t.status =  'Closed'
AND t.dept_id =  '1'
AND t.created >=  '2014-01-01 00:00:00'
AND TIMESTAMPDIFF( MINUTE , t.created, t.closed ) < SecondMax 
GROUP BY t.topic_id
ORDER BY Closed DESC");

I suspect the link " AND TIMESTAMPDIFF( MINUTE , t.created, t.closed ) < SecondMax " is the culprit but I am not sure.

The database table:

h.topic | created | closed

kid | 2014-01-01 00:05:00 | 2014-01-02 00:06:00

kid | 2014-01-01 00:05:00 | 2014-01-02 00:07:00

kid | 2014-01-01 00:05:00 | 2014-01-02 00:08:00

adult | 2014-01-01 00:05:00 | 2014-01-02 00:07:00

adult | 2014-01-01 00:05:00 | 2014-01-02 00:10:00

adult | 2014-01-01 00:05:00 | 2014-01-02 00:14:00

The desired result is

Help_topic | SecondMax

kid | 2

adult | 5

Thanks

Upvotes: 0

Views: 226

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

You have definitely identified the culprit. You cannot reference a column alias SecondMax in the where clause.

If you want the second longest times subject to these conditions, I would go for the group_concat() approach. You concatenate the strings durations together and then extract the second from them:

SELECT h.topic AS Help_Topic,
       substring_index(substring_index(group_concat(TIMESTAMPDIFF(MINUTE, t.created, t.closed )
                                                    order by TIMESTAMPDIFF(MINUTE, t.created, t.closed) desc
                                                   ), ',', 2
                                       ), ',', -1
                      ) as SecondMax
FROM ost_ticket t join
     ost_help_topic h
     on t.topic_id = h.topic_id
WHERE t.status =  'Closed' AND t.dept_id =  '1' AND t.created >=  '2014-01-01 00:00:00'
GROUP BY t.topic_id;

I also changed the join to use proper explicit join syntax.

Upvotes: 1

Related Questions