Max Row problem

[Crystalguru]

Howdy,

Trying to get the Max row out of a table.
For example:

ClientUID OrderUID Ordermaintidcode toucheddtm reason
4556 123 101 02-19-05 01:00 New
4556 123 102 02-19-05 02:00 Changed
4556 123 103 02-19-05 02:30 Final

What I need returned is the last row of information.(#103)

Here is my query:
SELECT CLIENTUID
,ORDERUID
,MAX(ORDERMAINTIDCODE)
,MAX(TOUCHEDWHEN)
,MAX(REASONTEXT)
FROM ORDERSTATUSHISTORY
WHERE CLIENTUID = 5000017718020001
AND ORDERUID = 1000855293068001
GROUP BY CLIENTUID, ORDERUID

What I have been getting with the above query is:
ClientUID OrderUID Ordermaintidcode toucheddtm reason
4556 123 103 02-19-05 02:30 Changed

Notice the reason field is from the row #102.

Need someone else's eyes to help here.

Thanks in advance.

 

[r937]

what are the criteria for "max row"?

i.e. which column or set of columns determines this?





rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[r937]

never mind, figured it out

select ClientUID
     , OrderUID
     , Ordermaintidcode
     , toucheddtm 
     , reason
  from ORDERSTATUSHISTORY as O
 where toucheddtm
     = ( select max(toucheddtm)   
           from ORDERSTATUSHISTORY
          where ClientUID = O.ClientUID
            and OrderUID = O.OrderUID )

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[Crystalguru]

Yup, that was it. I was going about getting the max from the wrong column.

Everything looks real good. Until I run into someone that has modified the # 102 and #103 column with the same dates. Then I get both rows returned.

ClientUID OrderUID Ordermaintidcode toucheddtm reason
4556 123 102 02-19-05 02:30 Changed
4556 123 103 02-19-05 02:30 Final

That's a real bi**h.
Damn users.

But low and behold. There's another date field to work with and they are always different. Alas! I overtake the users!

CreatedWhen

Thanks for your help. I am stuck over the weekend working. And I'm not a happy camper.

Enjoy.

 

[donutman]

It looks as though, Ordermaintidcode was intended for that purpose.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]