Joining tables based on two columns based on MAX for another column

Question

SELECT *
FROM HistoricCall h
INNER JOIN Responses r
ON h.projectid = r.projectId 
AND h.caseId = r.caseId

The HistoricCall table has a column for number of attempts for each time that we've called someone (h.callNumber).

I'm trying to write a query that would display:

SELECT 
    h.callDate,
    r.cellmap,
    COUNT(*)

but only the h.callDate that corresponds to the highest callNumber for each record.

HistoricCall:

projectId	caseId	callNumber	callDate
100018	0000000001	1	2014-09-11 16:32:11.000
100018	0000000001	2	2014-09-11 20:43:01.000
100018	0000000001	3	2014-09-12 21:09:55.000
100018	0000000001	4	2014-09-13 14:58:14.000
100018	0000000001	5	2014-09-14 16:42:27.000
100018	0000000001	6	2014-09-16 21:54:09.000
100018	0000000001	7	2014-09-16 22:30:24.000
100018	0000000002	1	2014-09-11 16:36:17.000
100018	0000000002	2	2014-09-13 18:05:44.000
100018	0000000002	3	2014-09-14 16:39:03.000

Responses:

projectId	caseId	cellmap
637850	0000000001	9101
637850	0000000002	9052
637850	0000000003	5071
637850	0000000004	5173
637850	0000000005
637850	0000000006	8062
637850	0000000007	0012
637850	0000000008	5292
637850	0000000009	9230
637850	0000000010	5187

Desired result:

projectId	callDate	cellmap	Count
637850	2014-09-11	0012	3
637850	2014-09-11	0014	7
637850	2014-09-12	0012	4
637850	2014-09-12	0014	2

It looks like your expected results are not related to your sample data — Alexey, Aug 22 '22 at 19:59
Please before considering posting: Pin down code issues via [mre]. Read the manual/reference & google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. Reflect research in posts. SO/SE search is poor & literal & unusual, read the help. Google re googling/searching, including Q&A at [meta] & [meta.se]. [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) [ask] [Help] — philipxy, Aug 23 '22 at 01:18
[Fetch the row which has the Max value for a column](https://stackoverflow.com/q/121387/3404097) etc — philipxy, Aug 24 '22 at 21:40

score 0 · Answer 1 · answered Aug 22 '22 at 21:25

You can convert the celldate to date from datetime and simple group by should work.

SELECT 
    Max(h.projectid) projectid, -- if you need project id of call you can also include it in the group by clause
    h.callDate,
    r.cellmap,
    COUNT(*)
FROM HistoricCall h
INNER JOIN Responses r ON h.projectid = r.projectId 
                       AND h.caseId = r.caseId
group by cast(h.callDate as date), r.cellmap

score 0 · Answer 2 · answered Aug 24 '22 at 15:44

You can create a join using a subquery to get the max callNumber by projectId and caseId grouping. There's no need to do a count since the callNumber is incremented according to your example above.

Fiddle for reference

SELECT h.projectId              AS "projectId",
       cast(h.callDate AS date) AS "callDate", --cast as date
       r.cellmap                AS "cellmap",
       m.maxCall                AS "Count"
FROM   HistoricCall h
       INNER JOIN Responses r
               ON h.projectid = r.projectId
                  AND h.caseId = r.caseId
       INNER JOIN (SELECT projectid,
                          caseId,
                          max(callNumber) maxCall --get max call number
                   FROM   HistoricCall
                   GROUP  BY projectid,
                             caseId) m
               ON h.projectId = m.projectId
                  AND h.caseId = m.caseId
                  AND h.callNumber = m.maxCall
WHERE  r.cellmap IS NOT NULL --remove if empty cellmap values can be included
GROUP  BY h.projectId,
          cast(h.callDate AS date),
          r.cellmap,
          m.maxCall
ORDER  BY h.projectId,
          cast(h.callDate AS date),
          r.cellmap ASC

Joining tables based on two columns based on MAX for another column

2 Answers2