The output should include the correct BID value... and it should take a very short time to process (very few seconds I hope).
I was using:
SELECT UID, BID, max(LoanTime) FROM tb GROUP BY LoanTime.
This returns very fast.
Unfortunately, this provides me with a random BID (first one that was found or something like that). I call upon the MySQL guys to think if this can be improved in future versions.
Then I was using:
SELECT * FROM tb tb1 WHERE LoanTime =(SELECT max(LoanTime) FROM tb tb2 WHERE UID = tb1.UID GROUP BY LoanTime)
This returns the correct rows.
Unfortunately, it takes forever!
Can anyone suggest an alternative (possibly by using additional tables) that will be both fast and provide the correct output...
In my implementation, the table tb is a memory table built from another table (I can use a temporary table just the same). So any combinations of keys can be used to best perform a fast data extraction.