How to obtain the highest record of a sequence

[1nonlymre]

Subject says it all. I need to be able to obtain the most current address record of each of our people in our database. Everytime we add a new record, there's a new sequencenumber given to that new entry. I need only the newest sequencenumber. How do i go about requesting this?

 

[lbass]

I'm assuming you are grouping by some people field (ID?). The simplest method is to use group selection (report->selection formula->GROUP) and enter:

{table.sequenceno} = maximum({table.sequencno},{table.ID})

A better method (faster report) would be create a SQL expression {%maxrecord}:

(
select max(`sequenceno`)
from table A
where A.`ID` = table.`ID`
)

Then go to report->selection formula->record and enter:

{table.sequenceno} = {%maxrecord}

-LB

 

[jannoth]

Hi lbass, I love your suggestion!

I prob have 50 reports which use group record selection, so will benefit from your suggested approach, thanks

I've used CR for years & read many CR books but remain stumpted by your idea

So ... is the rule that using Group Record Selection is generally slower than Record Selection based on a formula??

I didn't think you could use things like "maximum" or "average" at Record Selection stage so I've always worked around!

Thanks for elaborating/helping (if you get the chance) - it'd stop me posting more stupid questions in future!!

J

 

[1nonlymre]

I apologize for this lbass, but I am quite the idiot when it comes to this stuff. How do i use your suggestion. Could you point me in the right direction for learning how to apply your suggestions?

 

[1nonlymre]

Let me explain exactly what I am trying to do. I work for law enforcement in Probation. I want to compile a list of current people on probation. List their name, other info, and the current address.

When I add the address fields to the report, i get all the defendant's prior addresses, including the most recent one. I only want the most recent address to be listed for each individual. The field RecordSequenceNum holds the specific record number for each address added to any particular person and must be a vital key in determining which record must appear on the report.

That being said, one individual may have ten RecordSequenceNum values, meaning he has 10 different addresses. I want to list only #10 for that guy.

Another guy may only have 2 RecordSequenceNum values, and I want the highest value to be displayed only, etc....so on and so on.

Again, I want to apologize for not being so up to date on things. I'm not good at SQL whatsoever and can do simple Crystal Reports. However, I am a fairly quick learner and do want to learn new techniques all the time.

There...I've confessed, said my bit and wait for a reply. Thank you all in advance.

 

[fisheromacse]

lbass' sql method is the way to go, i think.

In the Field Explorer in crystal reports, right click on SQL Expression and choose 'New'

Type the expression lbass gave us, and change the values to match your tables/fields.

//{%maxRSN}
(
select max(`RecordSequenceNum`)
from yourtable A
where A.`PersonID` = yourtable.`PersonID`
)


Once the formula is created, goto the Report menu and choose Select Expert (or click the toolbar button)
click Show Formula
click Formula Editor
go to the last line, press Enter
then if you have any other selection criteria type "and" then select your table/field from the Report Fields, type "=" and then select the {%maxRSN} SQL expression field from the Report Fields.

(ALREADY EXISTING SELECTION CRITERIA)
AND
({table.RecordSequenceNum}={%MaxRSN})

 

[1nonlymre]

I'm really trying to follow you fisheromacse...here's what I typed with the following info: what you call the PersonID, I call PersonKey; what you call yourtable is PersonAddress with me. And we know that the sequence field is RecordSequenceNum.

So.....

Here is what I typed in the SQL Expression box:

//{%maxRSN}
(
select max('RecordSequenceNum')
from PersonAddress A
where A.'PersonKey' = PersonAddress.'PersonKey'
)

I follow the logic, but when I click on Save and Close, I get the following error:

Error in compiling SQL Expression:
Database Connection Error: '4200:[Microsoft][ODBC SQL Server Driver] Syntax error or access violation'.

So it seems some error is preventing me from creating the formula. The rest of your steps are very straigh-forward, but I still need to get past this one hurdle. Please advise! I know I'm close on this one.

 

[lbass]

Or if you are not ready to use a SQL expression, you can insert a group on the defendantID and then go to report->selection formula->GROUP and enter:

{table.Reordsequencenum} = maximum({table.recordsequencenub},{table.defendantID})

-LB

 

[1nonlymre]

I did what you said, lbass, went to (report->selection formula->GROUP) and typed the following:

{PersonAddress.RecordSequenceNum} = Maximum ({PersonAddress.RecordSequenceNum},{PersonAddress.PersonKey} )

but when i click on 'save and close' i get the following error:

"There must be a group that matches this field."

and everything from the word 'Maximum' on is highlighted in the formula.

The syntax is correct, right?

 

[lbass]

You have to have inserted a group on PersonKey before entering the group selection formula.

In your SQL expression, you have used single quotes(')--this is not the punctuation suggested (`). However, the punctuation you should use depends upon your datasource. Take a look at: database->show SQL query to see how to punctuate, and then use that in your SQL expression.

-LB

 

[1nonlymre]

Wait...wait a second. I hadn't realized that my personkey field had to be defined as a group for your last suggestion to work. I did just that and it seems to be working. Thank you. Don't know why the SQL statement didn't work, though.