Page 3 of 3
Posted: Fri Jul 30, 2004 1:18 pm
by feyd
if you didn't have the name and destination fields in the query, using distinctrow would produce all the right rows. You'd then need to add the name on your own via a lookup to find the longest string that matches a given number...
Posted: Fri Jul 30, 2004 1:30 pm
by jglicken
Code: Select all
mysql> select distinctrow a.calldate, a.src, a.dst, sec_to_time(a.billsec) as billsec, format((a.billsec/60 * b.rate + b.connectCost), 2) totalcost from cdr a left join rates b on substring(a.dst,4) regexp (concat('^', b.destination)) where src='erick' and dst like '011%' order by 'calldate' desc;
+---------------------+-------+--------------------+----------+-----------+
| calldate | src | dst | billsec | totalcost |
+---------------------+-------+--------------------+----------+-----------+
| 2004-07-27 10:52:52 | erick | 0115058885694 | 00:00:00 | 0.05 |
| 2004-07-27 10:52:23 | erick | 0115058885694 | 00:00:00 | 0.05 |
| 2004-07-27 10:51:13 | erick | 0115058885694 | 00:00:00 | 0.05 |
| 2004-07-27 10:46:45 | erick | 0115058885694 | 00:00:00 | 0.05 |
| 2004-07-03 13:47:34 | erick | 011582869237134 | 00:00:48 | 0.59 |
| 2004-07-03 13:46:01 | erick | 0115114229804 | 00:01:50 | 0.48 |
| 2004-06-30 00:10:42 | erick | 011582869237134 | 00:19:16 | 13.15 |
| 2004-06-29 23:50:15 | erick | 011582869237134 | 00:00:51 | 0.63 |
| 2004-05-09 00:11:40 | erick | 011582869237134 | 00:03:18 | 2.29 |
| 2004-04-17 13:52:56 | erick | 011582869237134 | 00:01:44 | 1.23 |
| 2004-04-17 13:50:20 | erick | 011582869237134 | 00:00:00 | 0.05 |
| 2004-04-17 13:48:21 | erick | 011584142869237134 | 00:00:00 | 0.05 |
| 2004-04-17 13:45:23 | erick | 011584142869237134 | 00:00:00 | 0.05 |
| 2004-04-16 18:51:01 | erick | 011584148681837 | 00:01:16 | 0.91 |
| 2004-04-16 10:23:00 | erick | 0115114220165 | 00:00:03 | 0.06 |
+---------------------+-------+--------------------+----------+-----------+
15 rows in set (2.34 sec)

thank you so much.
do you know where i can look up syntax on lookup?
i am trying google now?
jason
Posted: Fri Jul 30, 2004 1:45 pm
by feyd
Syntax on lookup? a lookup = a seperate select in this case..

something like:
Code: Select all
SELECT DISTINCT(destination), name FROM rates ORDER BY destination DESC
store off the results of that query, then run a comparison using [php_man]preg_match[/php_man]() or [php_man]ereg[/php_man]() against dst from each of the rows of the first one. Stop comparing (the first one's row x) when you find the first destination that matches.. add that destination's name to the first queries row while storing it off onto your final list for display.