Mysql Query Group by Last record and first record

Question

I have 2 tables which status table are connected with profile table. Profile table store unique tagId and status table store multiple duplicate tagid entry. I want to show group by tagid's last record and first record.

Table: profile Note: tagId is Unique

--------------------------------------------------------------------
| tagId(PK) | blah2 | blah3 | blah4 |
--------------------------------------------------------------------
101         |
102         |
103         |
104         |
105         |
106         |

Table: status

--------------------------------------------------------------------
statusId | tagId |  date      | height| weight |  statusType | blah2 | 
--------------------------------------------------------------------
1        | 101   | 2010-01-01 | 5.6   |  300   |  single     |
2        | 102   | 2010-01-01 | 5.7   |  300   |  single     | 
3        | 101   | 2015-01-01 | 5.6   |  310   |  married    |
4        | 103   | 2010-01-01 | 5.6   |  300   |  single     |
5        | 104   | 2010-01-01 | 5.6   |  300   |  single     |
6        | 101   | 2016-01-01 | 5.6   |  300   |  pregnant   |
7        | 101   | 2016-09-01 | 5.6   |  300   |  delivery   |
8        | 105   | 2010-01-01 | 5.6   |  300   |  single     |

What I want to try group by first date and group by last statusType

Query Result will be:

--------------------------------------------------------------------
| tagId |  date      | height| weight |  statusType | blah2 | 
--------------------------------------------------------------------
| 101   | 2010-01-01 | 5.6   |  300   |  delivery   |
| 102   | 2010-01-01 | 5.7   |  300   |  single     | 
| 103   | 2010-01-01 | 5.6   |  300   |  single     |
| 104   | 2010-01-01 | 5.6   |  300   |  single     |
| 105   | 2010-01-01 | 5.6   |  300   |  single     |

But I can't success with it, I tried with this MySQL code

SELECT DISTINCT Profile.TagId,Status.date,Status.StatusType,Status.height,Status.weight FROM Profile 
LEFT JOIN Status ON Status.TagId = Profile.TagId 
Where Status.StatusId In( Select Max(Status.StatusId) From Status Group By      Status.TagId ) 
Group By Status.TagId ORDER BY Profile.TagId ASC, Status.TagId DESC

But it returns last date and last statustype , like this

Query Result:

--------------------------------------------------------------------
| tagId |  date      | height| weight |  statusType | blah2 | 
--------------------------------------------------------------------
| 101   | 2016-09-01 | 5.6   |  300   |  delivery   |
| 102   | 2010-01-01 | 5.7   |  300   |  single     | 
| 103   | 2010-01-01 | 5.6   |  300   |  single     |
| 104   | 2010-01-01 | 5.6   |  300   |  single     |
| 105   | 2010-01-01 | 5.6   |  300   |  single     |

Answer 1

I think you are probably looking for something like this:

SELECT DISTINCT p.tagId, 
                smin.`date`, smin.height, smin.weight,
                smax.StatusType 
FROM Profile AS p
LEFT JOIN (
   SELECT tagId, MAX(`date`) AS max_date, MIN(`date`) AS min_date 
   FROM Status
   GROUP BY tagId 
) AS s ON p.tagId = s.tagId
LEFT JOIN Status AS smin ON smin.tagId = p.tagId AND s.min_date = smin.`date`
LEFT JOIN Status AS smax ON smax.tagId = p.tagId AND s.max_date = smax.`date`

The query uses a derived table which returns minimum and maximum date values per tagId. Using these values we can join back to Status table and get date, height and weight values from the Status record having the minimum date value and StatusType value for the Status record having the maximum date value.

Answer 2

Not sure if you want this or not, but just try it, and hope I do not mistake your question;)

SQL Fiddle

MySQL 5.6 Setup:

CREATE TABLE status
    (`statusId` int, `tagId` int, `date` date, `height` int, `weight` int, `statusType` varchar(8))
;

INSERT INTO status
    (`statusId`, `tagId`, `date`, `height`, `weight`, `statusType`)
VALUES
    (1, 101, '2010-01-01', 5.6, 300, 'single'),
    (2, 102, '2010-01-01', 5.7, 300, 'single'),
    (3, 101, '2015-01-01', 5.6, 310, 'married'),
    (4, 103, '2010-01-01', 5.6, 300, 'single'),
    (5, 104, '2010-01-01', 5.6, 300, 'single'),
    (6, 101, '2016-01-01', 5.6, 300, 'pregnant'),
    (7, 101, '2016-09-01', 5.6, 300, 'delivery'),
    (8, 105, '2010-01-01', 5.6, 300, 'single')
;

Query 1:

select f.`tagId`, date_format(l.`date`, '%Y-%m-%d') as `date`, f.`statusType`
from (
    select s.*
    from `status` s
    inner join (
        select max(`statusId`) as `statusId`, `tagId`
        from `status`
        group by `tagId`) t on s.`statusId` = t.`statusId` and s.`tagId` = t.`tagId`
    ) f 
inner join (
    select s.*
    from `status` s
    inner join (
        select min(`statusId`) as `statusId`, `tagId`
        from `status`
        group by `tagId`) t on s.`statusId` = t.`statusId` and s.`tagId` = t.`tagId`
    ) l on f.`tagId` = l.`tagId`
order by tagId

Results:

| tagId |       date | statusType |
|-------|------------|------------|
|   101 | 2010-01-01 |   delivery |
|   102 | 2010-01-01 |     single |
|   103 | 2010-01-01 |     single |
|   104 | 2010-01-01 |     single |
|   105 | 2010-01-01 |     single |

Subquery f is the first record by each group and l is the last record by each group, and if you want height or weight column, please choose one of them which you prefer to.

Also you can left join this all query to profile then you can get tagId = 106, but this record will give you null for column date and statusType.