Returning Max Value

[f0rg3tfu1]

Good Morning all,

This should be easy... Just running into issues.
CR 10, SQL Server 2003

In our work order system, update records constantly. We have the standard WORK ORDER table related to the COMMENTS field by a left outer join.

I am trying to return only the latest records out of the comments field.

EX:
WORK ORDER ID - 105205
Comment 1 - 5/5/05
Comment 2 - 5/6/05
Comment 3 - 5/7/05

Let me know if you need any more information.

Thanks for your help!!!

 

[synapsevampire]

There isn't a SQL Server 2003 to my knowledge, better return the CD for a refund to that fella with the trenchcoat

Where is the date stored, in the comments field?

Your example is incomplete, I appreciate that you bothered to show example data, but if you want technical help, please be technical by nature.

If it's a seperate field, then the following will return the latest comments:

maximum({table.datefield})

Perhaps you're trying to return the latest comments for a grouping, in which case use:

maximum({table.datefield},{table.groupfield})

Stating "just running into issues" doesn't help define your concern/problem, and posting technical information will help to quickly identify requirements:

Example data (show tables.fields and data)
Expected output

-k

 

[lbass]

If you insert a group on {table.Workorder}, then you can go to report->edit selection formula->GROUP and enter:

{table.date} = maximum({table.date},{table.workorder})

-LB

 

[f0rg3tfu1]

Lbass - Thank you very much that did the trick. I was originally trying to use the record selection which obviously didnt work. I really appreciate it!

Synapse - Forgive the typo... but we are running SQL 2000 along with WINDOWS Server 2003. Oversight on my part.

Funny how Lbass caught on to my problem without any snide comments isn’t it? I don't mean to criticize, especially those that are offering their assistance... but a little more tact might be in order for the benefit of other posters. This website is a fantastic tool for learning, and I don’t think we should do anything to discourage anyone from posting questions.

 

[synapsevampire]

This was basically the same as LB's suggestion:

maximum({table.datefield},{table.groupfield})

Secondly, this was meant jokingly "There isn't a SQL Server 2003 to my knowledge, better return the CD for a refund to that fella with the trenchcoat ".

Forgive me, I hadn't meant to offend you, and do everything possible to encourage people to include technical information in their posts.

Glad that you worked it out, and I'll try not to upset you in the future.

-k