0

I have this table calls

id | clientid | salespersonid | duration | statusid | last_update
1  | c11      | sp99          | 05:00    | 1        | yyyy
2  | c11      | sp99          | 06:00    | 1        | yyyy
3  | c11      | sp99          | 07:00    | 3        | yyyy
4  | c12      | sp99          | 08:00    | 3        | yyyy

I'm performing this query to count the number of calls per client for each salesperson

select *,count(wpc.id) as num_of_calls 
                                    from calls wpc
                                    where wpc.salespersonid=?
                                    group by wpc.clientid
                                    order by wpc.last_update desc

This returns this result

id | clientid | salespersonid | duration | statusid | last_update|num_of_calls
1  | c11      | sp99          | 05:00    | 1        | yyyy       |3
4  | c12      | sp99          | 08:00    | 3        | yyyy       |1

The query is fine but I'm not satisfied with the order in which the program counts and displays the rows. It displays the first row of the count. I'm wanting it to display the last row of each count.

So the result should be

id | clientid | salespersonid | duration | statusid | last_update|num_of_calls
3  | c11      | sp99          | 07:00    | 3        | yyyy       |3
4  | c12      | sp99          | 08:00    | 3        | yyyy       |1

How can I display the last row of each count instead of the first one?

Sadie
  • 163
  • 10
  • Would the `limit` clause work for you? – 7 Reeds Jul 30 '18 at 16:08
  • @7Reeds: Yes as long as it returns the num_of_calls properly yes. – Sadie Jul 30 '18 at 16:09
  • 2
    You can't guarantee either with this kind of query. While it usually works out that the non-aggregated non-grouped fields are the first _encountered by MySQL_; MySQL does not guarantee that at all. Any non-aggregated non-grouped fields should be considered a random choice of the values seen for those fields with the grouped combination of fields.... `SELECT a, b, c FROM t GROUP BY a` does not even guarantee b and c will be from the same row. If you want the last (or first row) of each group, you typically need a subquery that identifies that row, that is then joined against to get that row. – Uueerdo Jul 30 '18 at 16:30
  • This query is NOT fine!!! – Strawberry Jul 30 '18 at 16:37
  • @Strawberry: Okay? Mind giving more details? By the way the MAX still will return same rows 1 and 4. The desired result is row 3 and 4. – Sadie Jul 30 '18 at 16:47
  • @Uueerdo: Thanks for this sound comment. Would you mind showing it to me? I tried with MAX and it does not work. – Sadie Jul 30 '18 at 16:54
  • That's because you've not studied the accepted answer at the linked question. – Strawberry Jul 30 '18 at 16:54
  • The answer @Strawberry linked covers exactly what you're asking. – Uueerdo Jul 30 '18 at 16:55
  • 1
    http://sqlfiddle.com/#!9/b14706/2 – Strawberry Jul 30 '18 at 17:02

1 Answers1

0

Duplicate of Using ORDER BY and GROUP BY together

Using solution: https://stackoverflow.com/a/35053900/2397717

 SELECT wpc.*, COUNT(wpc.id) AS num_of_calls FROM (SELECT * FROM calls ORDER BY last_update DESC) as wpc GROUP BY wpc.clientid
Sookie Singh
  • 1,543
  • 11
  • 17