0

I have 3 table named Sale, SaleStatus, SaleStatusType. The table structure is like below

Sale Status:
   SaleStatusID
   SaleStatusTypeID
   CreateDate
   SaleID
SaleStatusType:
   SaleStatusTypeID
   SaleStatusTypeName
Sale:
   SaleID
   <other sale info>

There can be multiple SaleStatus for one SaleID. Now if I want to select each saleID and its latest SaleStatus information added to my database based on CreateDate (DATETIME type), what should I do? I've written something like the following which results in error

SELECT SST.SaleStatusType, SS.SaleStatusTypeID, SS.SaleID
FROM SaleStatusType AS SST
   INNER JOIN SaleStatus AS SS 
      ON SS.SaleStatusTypeID = SST.SaleStatusTypeID
GROUP BY S.SaleID
HAVING SS.CreateDate = MAX(SS.CreateDate)
Mateen Bagheri
  • 99
  • 1
  • 10
  • 1
    your group by has the wrong alias, it should be `SS`, but it will still error because you are selecting columns outside of the group by without a `max()` or `min()`. Instead you should do a partition over `SS.CreateDate` with a where clause. https://www.sqlshack.com/sql-partition-by-clause-overview/ – micah Jun 27 '22 at 13:23
  • 1
    Your approach is not the best way to handle this kind of thing. Try [this approach](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) instead. – Sean Lange Jun 27 '22 at 14:58
  • Thanks for your answers. Yeah this new approach seems to be working. I'm kinda new to writing raw sql these little tips help alot – Mateen Bagheri Jun 28 '22 at 06:07

0 Answers0