SQL query from two tables ordering results by both tables columns

Question

I have two tables for registering out-going phone calls.

First table is people. The scheme is:

-----------------------------------
id | fname | lname | phone_number |
-----------------------------------
1  | John  | Black | 132333312    |
2  | Marry | White | 172777441    |
-----------------------------------

Second tables called calls. The scheme is:

----------------------------------------------------------------------
id | scr_phone | dst_phone | dst_public_id | date      | notes       |
----------------------------------------------------------------------
1  | 555       | 132333312 | 1             | 1.12.2013 | chit-chat   |
2  | 555       | 172777441 | 2             | 1.12.2013 | serios talk |
3  | 555       | 172777441 | 2             | 2.12.2013 | conversation|
----------------------------------------------------------------------

I'm displaying list of phones for users not in an alphabetical order but by frequency of calls. So whoever calls from local phone 555 sees at the top the people who he/she calls more often. Here is the MySQL query I use for it:

SELECT p.fname, p.lname, c.notes, COUNT(c.dst_public_id) AS amount
FROM people p
JOIN calls c ON c.dst_public_id = p.id
WHERE phones != ''
GROUP BY p.id
ORDER BY amount DESC, p.lname ASC

As a result I get person number 2 at the top of the phones list and person number 1 is on second place (I count how many calls are for each individual public and order it by that amount). Here is the query result:

--------------------------------
fname | lname | notes | amount |
--------------------------------
Marry | White |       |  2     |
John  | Black |       |  1     |
--------------------------------

This is all good. But I want always to display last note made on last conersation with that person. I can probably make a totally separated MySQL query for each person requesting last record of each and getting the data from that, something like:

SELECT notes FROM calls WHERE dst_public_id = 2 ORDER BY date DESC LIMIT 1

... and then add the result of the first query inside my PHP script, but is there any way to do it with one single query?

This answer might be what you are looking for: http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group — Aiias, Nov 30 '13 at 21:58

score 2 · Accepted Answer · answered Nov 30 '13 at 21:59

2

You can get the last note with the following MySQL trick, using group_concat() and substring_index():

SELECT p.fname, p.lname, c.notes, COUNT(c.dst_public_id) AS amount,
       substring_index(group_concat(notes order by id desc separator '^'), '^', 1) as last_notes
FROM people p JOIN
     calls c
     ON c.dst_public_id = p.id
WHERE phones != ''
GROUP BY p.id
ORDER BY amount DESC, p.lname ASC;

For this to work, you need a separator character that will never appear in the notes. I have chosen '^' for this purpose.

answered Nov 30 '13 at 21:59

Gordon Linoff

1,242,037
58
646
786

How efficient is this? Is it really concatenating all notes per phone just to find the last one? And I still wonder if my query is a solution too (I've seen this approach taken by many DBAs), unfortunately cannot test it right now. – peter.petrov Nov 30 '13 at 22:27
THis one works fine. I will try the second one too. And I just realized I have a little question additionally to that. If different users are calling i.e. src_phones are different how can I order most called phones for that particular user? Eveything above orders it by for all users without considering each one separatly. – user164863 Nov 30 '13 at 22:33
I think it works fine because you don't have 10000 users each of which has about 1000 calls or so. Not sure how much data you have in your DB. Anyway. I don't know what src_phones is. You wrote scr_phones in your question. My approach uses grouping and nested queries. It should be possible to do what you need with these two. I am just curious if I got that query right without SQL in front of me :) – peter.petrov Nov 30 '13 at 22:34
@user164863 . . . I think you would filter on the user id in the where clause to get this for a particular user. – Gordon Linoff Nov 30 '13 at 22:35
ok, but I would like to show phones for that particular user and then all the rest – user164863 Nov 30 '13 at 22:40
This scr_phone looks like an area code to me. But maybe I am off base. – peter.petrov Nov 30 '13 at 22:41
those are local phone numbers, I could use user_ids instead – user164863 Nov 30 '13 at 22:43
OK, if I get it right, your original question was to get the data aggregated by 'person called'. And now you need some aggregated data by 'person calling'. If so, you might use similar techniques, I guess, either from the accepted answer or from mine. – peter.petrov Nov 30 '13 at 22:47
Yes, correct. I have figured it out. I have added "... AND c.src_phone = [local user phone]" to the JOIN part and replaced JOIN to LEFT JOIN and all is working great. – user164863 Dec 01 '13 at 11:08

peter.petrov · Answer 2 · 2013-11-30T22:30:59.907

1

Can you try this? I haven't tested it as I don't have SQL in front of me but you should get the idea.

SELECT tbl.notes, tbl.amount, p.fname, p.lname

FROM people p

join
(
select t.dst_public_id, c1.notes, t.cnt as amount
from calls c1 join 
   (
      SELECT c2.dst_public_id, count(c2.id) as cnt, max(c2.id) as id
      FROM 
      calls c2
      group by c2.dst_public_id
   ) t on c1.id = t.id
) tbl on p.id = tbl.dst_public_id

WHERE p.phones != ''
GROUP BY p.id
ORDER BY tbl.amount DESC, p.lname ASC

edited Nov 30 '13 at 22:30

answered Nov 30 '13 at 22:10

peter.petrov

38,363
16
94
159

Yes, this one working great too. I like the forst answer for being more compact, I get same time for query but this is true, I have only about 30 users and maybe tens of phone calls daily. – user164863 Nov 30 '13 at 22:42
OK, thanks. Just wanted to make sure this query is OK too or delete it from here if wasn't. – peter.petrov Nov 30 '13 at 22:44
You know what, Peter? Your query actually works faster, if I restart MySQL in order to clear its cach I get 0.0318 sec on yours and 4.7 sec for the Gordon's one. – user164863 Dec 01 '13 at 11:14
Well, I am not surprised because he's concatenating strings to get what you wanted. Also, his code seems MySQL dependent, mine is not. I don't know if that's any factor for your case. Good luck! – peter.petrov Dec 02 '13 at 08:01

SQL query from two tables ordering results by both tables columns

2 Answers2