CODEWITHSUNDEEP
mysqlselect
user268451
user268451

Reputation: 789

only select row with later expiry date

if I have a table like the following, how could I only select out those serial_number and its contract_type with later expiry date? 

serial_number      contract_type         expiry_date
abc001                  SPRT              2011-05-31 00:00:00
abc001                  HOMD              2013-05-31 00:00:00
abc002                  SPRT              2012-10-14 00:00:00
abc002                  HOMD              2011-10-14 00:00:00
abc003                  SPRT              2014-05-31 00:00:00
abc003                  HOMD              2011-05-31 00:00:00
................

1) I could make the assumption if it makes this query simpler: each serial_number(SN) will have two and only two contract_types in the table.

2) the actual situation is: SN and contract_type are the primary key, and I'm only looking for the contract_type 'SPRT' and 'HOMD'.

The final result set I need is:

Anyone could give out the query? the actual case might be too complicated to get in one query, but how about the first simplified case.

Upvotes: 2

Views: 179

Answers (1)

Joe Stefanelli
Joe Stefanelli

Reputation: 135818

SELECT t.serial_number, t.contract_type, t.expiry_date
    FROM YourTable t
        INNER JOIN (SELECT serial_number, MAX(expiry_date) AS MaxDate
                        FROM YourTable
                        WHERE contract_type IN ('SPRT', 'HOMD')
                        GROUP BY serial_number) q
            ON t.serial_number = q.serial_number
                AND t.expiry_date = q.MaxDate
    WHERE t.contract_type IN ('SPRT', 'HOMD')

Upvotes: 1

Related Questions