What's The Fastest Way To Obtain The Maximum Value Of A Column In A Table?

INFORMIX-SQL 4.10.DC1 (SE Engine), on DOS 6.22, under Microsoft Virtual PC 2007, in Windows 7! EDIT: Looking for Jonathan Leffler's wisdom on this one! I have a child table called

Solution 1:

Since you are wanting the maximum transaction number for a fk_id, I think I would expect to create an (optionally UNIQUE) index:

CREATE {UNIQUE} INDEX ix_lookup1 ON Transaction(FK_ID, Transaction_Number);

The index can be unique since transaction number alone is unique, but the point of this index is to speed up lookups for a given fk_id value.

I would then expect to use a query like the one that oers suggested:

SELECT * 
  FROM customer AS c, transaction AS t
 WHERE c.pk_id = t.fk_id
   AND c.pk_id = 12345678         -- crucial to limit the data to one return row
   AND t.transaction_number =
          (SELECT MAX(transaction_number)
             FROM transaction AS t2
            WHERE t2.fk_id = c.pk_id)

This is still a correlated sub-query, but given that the customer number is used, there will only be one row of data to return and it is blatant to the optimizer that this is the case. Depending on the programming language I was using, I might well arrange for the sub-query to use:

SELECT * 
  FROM customer AS c, transaction AS t
 WHERE c.pk_id = t.fk_id
   AND c.pk_id = 1234567AND t.transaction_number =
          (SELECT MAX(transaction_number)
             FROM transaction AS t2
            WHERE t2.fk_id = 1234567)

Or even:

SELECT * 
  FROM customer AS c, transaction AS t
 WHERE c.pk_id = t.fk_id
   AND c.pk_id = 1234567AND t.transaction_number =
          (SELECT MAX(transaction_number)
             FROM transaction AS t2
            WHERE t2.fk_id = 1234567)

You could use placeholders or create the SQL from a string as suits.

Even better, I'd avoid the problem by capturing the inserted serial value immediately after the insert so that (a) if the program ever went multi-user, it would still work and (b) the whole MAX issue would be avoided, and the query could become:

SELECT * 
  FROM customer AS c, transaction AS t
 WHERE c.pk_id = t.fk_id
   AND c.pk_id = 1234567AND t.transaction_number = 23456789;

Note that with the c.pk_id = 1234567 for the customer specified as a parameter to the query, you might not even need the index I suggested. And wherever I used a number, it would be fine with me to use a placeholder for the value to be supplied when the query is executed.

Remember, you are using software that is old enough to drink, let alone vote; the optimizer is not as good as the one you'd find in a more modern DBMS.

Solution 2:

Can you try using JOIN instead of the (implicit JOIN with) WHERE:

SELECT*FROM customer
  JOIN transaction
    ON customer.pk_id = transaction.fk_id
WHERE transaction.transaction_number = ( SELECTMAX(transaction_number)
                                         FROM transaction)

or this:

SELECT * 
FROM customer
  JOIN transaction
    ON customer.pk_id = transaction.fk_id
  JOIN ( SELECT MAX(transaction_number) AS max_tn
         FROM transaction
       ) AS tr
    ON transaction.transaction_number = tr.max_tn

---

Without using JOINs, I can think of this:

SELECT*FROM customer
     , transaction
 WHERE customer.pk_id = transaction.fk_id
   AND transaction.transaction_number IN 
       ( SELECTMAX(transaction_number)
         FROM transaction
       )

Perhaps the IN (SELECT ...) is better optimized than = (SELECT ...)

---

You can also try putting the tables (and conditions) in reverse order:

SELECT*FROM transaction, customer 
 WHERE transaction.transaction_number
       = ( SELECTMAX(transaction_number)
           FROM transaction )
  AND customer.pk_id = transaction.fk_id

---

No idea if the following will work or if it will raise error but it's worth trying:

SELECT * 
FROM 
      ( SELECT MAX(transaction_number) AS max_tn
        FROM transaction
      )
  AS tr
   , transaction
   , customer
WHERE transaction.transaction_number = tr.max_tn
  AND customer.pk_id = transaction.fk_id