0

As you can see my query, this is the working one

SELECT m.MerchandiseId, m.MerchandiseName, createdAt =  MAX(i.CreatedAt) FROM Inbox i
INNER JOIN [User] u
ON i.FromUserId = u.UserId
LEFT OUTER JOIN Merchandise m
ON u.MerchandiseId = m.MerchandiseId  OR i.ToMerchantId = m.MerchandiseId
WHERE i.ToCompanyId = 10 OR i.FromCompanyId = 10
GROUP BY m.MerchandiseId, m.MerchandiseName

This is the result after query the above statement

What if i want to SELECT one more field as i.InboxMessage with the Last record of each group?

SELECT m.MerchandiseId, m.MerchandiseName, i.InboxMessage, createdAt =  MAX(i.CreatedAt) FROM Inbox i
INNER JOIN [User] u
ON i.FromUserId = u.UserId
LEFT OUTER JOIN Merchandise m
ON u.MerchandiseId = m.MerchandiseId  OR i.ToMerchantId = m.MerchandiseId
WHERE i.ToCompanyId = 10 OR i.FromCompanyId = 10
GROUP BY m.MerchandiseId, m.MerchandiseName

I've tried some ways that I found in stackoverflow, but I still not manage to do it. Thousand of thanks if anyone would help on this.

ypercubeᵀᴹ
  • 99,450
  • 13
  • 217
  • 306
jefferyleo
  • 101
  • 1

1 Answers1

-1

FYI, I just solve my issue by using the subquery

SELECT m.MerchandiseId, m.MerchandiseName, (SELECT TOP 1 e.InboxMessage FROM Inbox e WHERE (e.ToMerchantId = m.MerchandiseId AND e.FromCompanyId = 10) OR (ToCompanyId = 10 AND FromMerchantId = m.MerchandiseId) ORDER BY e.CreatedBy ASC) AS InboxMessage, createdAt =  MAX(i.CreatedAt) FROM Inbox i
INNER JOIN [User] u
ON i.FromUserId = u.UserId
LEFT OUTER JOIN Merchandise m
ON u.MerchandiseId = m.MerchandiseId  OR i.ToMerchantId = m.MerchandiseId
WHERE i.ToCompanyId = 10 OR i.FromCompanyId = 10
GROUP BY m.MerchandiseId, m.MerchandiseName

Here's the code that I worked on

jefferyleo
  • 101
  • 1