SQL Join Min and Max returning all variants

Question

I have two tables (Records and Comments). I am trying to join the two using Min and Max on the Comments id, where the result would be:

Record id; Date; First Comment; Last Comment

Records:

id  date
1   1/1/15
2   1/2/15

Comments:

id  Rec_id  Comment
1   1       First Comment
2   1       Last Comment
3   2       First Comment
4   2       Second Comment
5   2       Last Comment

Here is the script:

SELECT  
    Records.id
    , Records.rcd_date
    , MIN(Comments_1.id) AS minID
    , Comments_1.Comment AS minCom
    , MAX(Comments_2.id) AS maxID
    , Comments_2.Comment AS maxCom
FROM    
    Records
INNER JOIN
    Comments AS Comments_1 ON Records.id = Comments_1.Rec_id 
INNER JOIN
    Comments AS Comments_2 ON Records.id = Comments_2.Rec_id
GROUP BY  
    Records.id
    , Records.rcd_date
    , Comments_1.Comment
    , Comments_2.Comment

All I want to see is:

id  rcd_date    minID   minCom          maxID   maxCom
1   2015-01-01  1       First Comment   2       Last Comment
2   2015-01-02  3       First Comment   5       Last Comment

But I am getting every variation on the comments to the result table.

id  rcd_date    minID   minCom          maxID   maxCom
1   2015-01-01  1       First Comment   1       First Comment
1   2015-01-01  1       First Comment   2       Last Comment
1   2015-01-01  2       Last Comment    1       First Comment
1   2015-01-01  2       Last Comment    2       Last Comment
2   2015-01-02  3       First Comment   3       First Comment
2   2015-01-02  3       First Comment   5       Last Comment
2   2015-01-02  3       First Comment   4       Second Comment
2   2015-01-02  5       Last Comment    3       First Comment
2   2015-01-02  5       Last Comment    5       Last Comment
2   2015-01-02  5       Last Comment    4       Second Comment
2   2015-01-02  4       Second Comment  3       First Comment
2   2015-01-02  4       Second Comment  5       Last Comment
2   2015-01-02  4       Second Comment  4       Second Comment

I have tried Inner Join, Right Outer Join, and Left Outer Join, but the results are all the same.

Any ideas?

possible duplicate of [Retrieving the last record in each group](http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group) — Bulat, Aug 22 '15 at 04:31

score 0 · Answer 1 · answered Aug 22 '15 at 03:47

Use a subquery to get the ids, and then left join to bring in the values:

SELECT r.*, ccmin.id as minid, ccmin.comment as mincom,
       ccmax.id as maxid, ccmax.comment as maxcom
FROM Records r LEFT JOIN
     (SELECT rec_id, MIN(id) as minid, MAX(id) as maxid
      FROM comments
      GROUP BY rec_id
     ) cc
     ON r.id = cc.rec_id LEFT JOIN
     comments cmin
     ON cmin.id = cc.minid LEFT JOIN
     comments cmax
     ON cmax.id = cc.maxid and cc.minid <> cc.maxid;

score 0 · Answer 2 · answered Aug 22 '15 at 04:39

You can do it using subquery in the on clause of join:

select r.id,
       r."date"     rec_date,
       c1.id        min_id,
       c1."Comment" min_comment,
       c2.id        max_id,
       c2."Comment" max_comment
  from Records r
  join Comments c1
    on r.id = c1.Rec_id
   and c1.id = (select min(id) from Comments c where c.rec_id = r.id)
  join Comments c2
    on r.id = c2.Rec_id
   and c2.id = (select max(id) from Comments c where c.rec_id = r.id)

The result:

| ID |                   REC_DATE | MIN_ID |   MIN_COMMENT | MAX_ID |  MAX_COMMENT |
|----|----------------------------|--------|---------------|--------|--------------|
|  1 |  January, 01 2015 00:00:00 |      1 | First Comment |      2 | Last Comment |
|  2 | February, 01 2015 00:00:00 |      3 | First Comment |      5 | Last Comment |

This is the demo.

score 0 · Answer 3 · answered Aug 22 '15 at 06:44

select A.id,a.[date],a.minid
,(select top 1 Comment from comments  where comments.id=a.minid) minCom
,a.maxid
,(select top 1 Comment from comments  where comments.id=a.maxid) maxcommt
from 
(
    select Records.id,Records.[date],min(Comments.id) minid,max(Comments.id)     maxid,rec_id
    from Records
    inner join 
    Comments on Records.id=Comments.rec_id
    --where Records.id=2
    group by Records.id,Records.[date],rec_id
)A

SQL Join Min and Max returning all variants

3 Answers3