CODEWITHSUNDEEP
mysqlmax
user1772498
user1772498

Reputation: 47

Select most recent qualified type within a date range

I have a data set that contains IDs, Status Dates (ordinal) and Status Types.

I am trying to select a subset of data that contains IDs within a date range where the most recent Status Type is Open or Transfer.

SELECT id, 
       status_date, 
       Date_format(Str_to_date(status_date, '%Y%j'), '%m/%d/%Y'), 
       status_type
FROM   my.TABLE 
WHERE  ( ( ( status_type = 'O' ) 
            OR ( status_type = 'T' ) ) 
         AND ( status_date <= 2012182 ) )

Should I use MAX(Status_Date) or the LAST(Status_Date) function?

Upvotes: 0

Views: 50

Answers (1)

Kermit
Kermit

Reputation: 34063

LAST is not a valid MySQL function, so MAX would be the appropriate function. You also don't need all those parentheses. 

WHERE  ( status_type = 'O' 
          OR status_type = 'T' ) 
       AND status_date <= 2012182

You can alternatively use IN to specify a list for status_type.

WHERE  status_type IN ('O', 'T') 
       AND status_date <= 2012182

Upvotes: 1

Related Questions