0

I have a table that a primary key that can have one to two different status' associated with it. It looks like this:

Key Status
123 Book
123 Change
122 Book
121 Book
121 Change

If there is a record that has status of 'Change' then I only want to select that record and not the record with the same key that has a status of 'Book'. If the key only has a status of 'Book' then I always want to choose that record. It is not possible to have a status of 'Change' without having a status of 'Book' first.

This is what I would like to see returned:

Key Status
123 Change
122 Book
121 Change

Any help?

Hannah Vernon
  • 70,041
  • 22
  • 171
  • 315
user158656
  • 9
  • 1
  • To obtain fully correct answer you must specify: 1) your DBMS (including version) 2) full list of possible Status values (or clarify it is dynamic). – Akina Aug 17 '18 at 04:22

1 Answers1

2
SELECT Key, MAX(Status) FROM Table
GROUP BY Key

Nice and simple - works because Change is after Book when ordered by the MAX aggregate function.

George.Palacios
  • 5,540
  • 21
  • 48