How to select the higher value from group of random letters with Informix?

Question

I have a column in a table that represents the type of inspeccion of a product.

product | status
-----------------
pen     | F
pen     | G
pen     | S
pencil  | A
pencil  | G
erase   | F
erase   | S
erase   | F

In one process, each inspection is done in different fixed steps. But the letters aren't ordered according to these steps. They're order like this:

1st step = G

2nd step = S

3rd step = F

4th step = A

I'd like to select the most advanced status of each product, reproducing for example, the following result for this table:

product | status
----------------
pen     | F        - which means the pen's already in the 3rd step
pencil  | A        - which means the pencil's already in the 4th step
erase   | F        - which means the erase's already in the 3rd step

How can I do that?

Answer 1

You will need a table to sequence the letters (e.g. status_sequence):

 status    sequence
 A         4
 F         3
 S         2
 G         1

You then combine that with your main query, and order on the status_sequence.sequence column. You can use MAX on it too.

DROP TABLE IF EXISTS status_sequence;

CREATE TABLE status_sequence
(
    STATUS CHAR(1) NOT NULL PRIMARY KEY,
    SEQUENCE INTEGER NOT NULL UNIQUE
);

DROP TABLE IF EXISTS product_status;
CREATE TABLE product_status
(
    product VARCHAR(10) NOT NULL,
    STATUS  CHAR(1) NOT NULL REFERENCES status_sequence(STATUS),
    PRIMARY KEY(product, STATUS)
);

INSERT INTO status_sequence VALUES('A', 4);
INSERT INTO status_sequence VALUES('F', 3);
INSERT INTO status_sequence VALUES('S', 2);
INSERT INTO status_sequence VALUES('G', 1);

INSERT INTO product_status VALUES('pen', 'F');
INSERT INTO product_status VALUES('pen', 'G');
INSERT INTO product_status VALUES('pen', 'S');
INSERT INTO product_status VALUES('pencil', 'A');
INSERT INTO product_status VALUES('pencil', 'G');
INSERT INTO product_status VALUES('erase', 'F');
INSERT INTO product_status VALUES('erase', 'S');

SELECT ps.product, ps.STATUS
  FROM product_status AS ps
  JOIN status_sequence AS ss ON ps.STATUS = ss.STATUS
  JOIN (SELECT p2.product, MAX(s2.SEQUENCE) AS max_seq
          FROM product_status  AS p2
          JOIN status_sequence AS s2 ON s2.STATUS = p2.STATUS
         GROUP BY p2.product
       ) AS ms
    ON ss.SEQUENCE = ms.max_seq AND ps.product = ms.product
 ORDER BY ms.max_seq

Output:

erase      F
pen        F
pencil     A

Note that you can sort by a column that is not selected.