Select Records with highest ID

[kwirky]

Hi,
Am trying to amend a report so that only the record with the highest row ID is shown. I have a table with listings of quotes created and in many cases there are revisions of the same quote number and I want to show only the most recent revision. There are no identifiers within the table to be able to exclude the others, the only thing that will differ would be the price (sometimes higher, sometimes lower). Sometime the revisions are done on the same date, so I cannot select by recent date.

ID Date
381704 1/7/2010
382338 1/7/2010
382339 1/7/2010

I though that I could use a select statement for maximum ID, however cannot word it properly.

Any help would be appreciated
Kwirky

 

[Turkbear]

Hi,
Is the quote Number part of the record?

If so, then group on that and restrict the details to Max(ID) using a Group selection formula like:
ID = Maximum(ID).

Or you could use a command object like:

Select Quote_Number,max(ID) from table
group by Quote_Number

( Your syntax may vary,depending on the database used.)



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[kwirky]

Thank you so much. It worked simply by grouping on the quote number and then sorting the ID ascending.
I then suppressed the group header and the details section.

Fantastic.