query to find the maximum time based on the each businessurl

create table test1 ( time_in_sec bigint, transid,businessurl);

The table data are as folows:

mysql> select * from tmp_test;
+-------------+---------+-------------+
| time_in_sec | transid | businessurl |
+-------------+---------+-------------+
| 10 | 1 | htt:/test1 |
| 20 | 2 | htt:/test1 |
| 33 | 3 | htt:/test1 |
| 33 | 4 | htt:/org1 |
| 33 | 5 | htt:/org1 |
| 100 | 6 | htt:/org1 |
| 100 | 6 | htt:/org2 |
+-------------+---------+-------------+

query stage1: (we want some of time based on transid and businessurl)

mysql> select sum(time_in_sec), transid,businessurl from tmp_test group by transid,businessurl;
+------------------+---------+-------------+
| sum(time_in_sec) | transid | businessurl |
+------------------+---------+-------------+
| 10 | 1 | htt:/test1 |
| 20 | 2 | htt:/test1 |
| 33 | 3 | htt:/test1 |
| 33 | 4 | htt:/org1 |
| 33 | 5 | htt:/org1 |
| 100 | 6 | htt:/org1 |
| 100 | 6 | htt:/org2 |
+------------------+---------+-------------+
7 rows in set (0.00 sec)


from the above query we want the output of maximum time for each businessurl


the output should be as follows:

33 | 4 | htt:/org1
100 | 6 | htt:/org2


solution:

the above query can be writte in two ways:

1)
select
max(total) total,
cast(substring_index(group_concat(transid ORDER BY total desc), ',',1) as unsigned) transid,
businessurl from
(
select
sum(time_in_sec) total, transid, businessurl
from
tmp_test group by transid,businessurl ) Z
group by businessurl


2)
DROP TEMPORARY TABLE IF EXISTS tmp_table2;
CREATE TEMPORARY TABLE tmp_table2 AS SELECT sum(time_in_sec) total, transid, businessurl FROM tmp_test GROUP BY transid,businessurl;

CREATE INDEX in_business_total
  ON tmp_table2(businessurl, total);

SELECT Z.total,
      Z.businessurl,
      (SELECT min(t2.transid)
         FROM tmp_table2 t2
        WHERE t2.businessurl = Z.businessurl AND t2.total = Z.total)
         transid
 FROM (SELECT max(t.time_in_sec) total, t.businessurl
         FROM tmp_test t
       GROUP BY t.businessurl) Z;