Q: How do I get a list of primary_ticket_numbers and revisions from the ticket master file such that I get one record for each primary, and the revision is the latest revision, regardless of whether the ticket was canceled?

A: Use a group by and having statement, like the following

select primary_ticket_number, revision
from tckmast
group by primary_ticket_number
having revision = max(revision)

What this does is group all the records by primary_ticket_number, so that if a ticket has 10 revisions, it groups those 10 revisions together. The having is like a where clause that applies to EACH group.

Last updated: 04/28/05