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 |
height
andweight
? – Blank