Select MAX Value for Each Account

[CrystalVis]

Hello All, here is a sample data in my table:

account_id transaction created_date
2025 846011 2003-08-14 07:28:12.037
2025 846012 2003-08-14 07:28:18.300
2025 846013 2003-08-16 20:18:25.253

6375 319711 2003-08-19 18:02:16.503
6375 319712 2003-08-19 18:02:22.223
6375 319713 2003-08-20 09:05:44.150

What I'm trying to accomplish is select the row with the latest created_date. so the result I want is as follow:
2025 846013 2003-08-16 20:18:.300
6375 319713 2003-08-20 09:05:44.150


this is what i have right now

SELECT
omh.account_id,
omh.transaction,
omh.created_date
FROM order_mgt_history omh
WHERE omh.created_date IN
(SELECT
Max(created_date)
FROM order_mgt_history
GROUP BY account_id)
ORDER BY omh.account_id

the result i get from this query is not consistent. Can you please tell me what's wrong with this query. Any help/suggestion is greatly appreciated.

 

[rac2]

I think you are looking for a correlated sub-query. You need to add a condition to the sub-query to make it correlate.

SELECT 
    omh.account_id, 
    omh.transaction,
    omh.created_date
FROM order_mgt_history omh
WHERE omh.created_date IN  
    (SELECT 
      Max(created_date)
     FROM order_mgt_history
     WHERE account_id = omh.account_id)
ORDER BY omh.account_id

And since MAX() returns a single row, you can use the = operator.

 . . .
WHERE omh.created_date =
 . . .

Or you could JOIN the sub-query you have instead of placing it in the WHERE clause.

SELECT 
    omh.account_id, 
    omh.transaction,
    omh.created_date
FROM order_mgt_history omh
JOIN (SELECT account_id,
      Max(created_date) AS "latest_date"
     FROM order_mgt_history
     GROUP BY account_id) momh  ON
     omh.created_date = momh.latest_date
ORDER BY omh.account_id

Toss those two approaches into Query Analyzer and look at the relative cost. I will guess they are equal, 50% each.

Let me know.