[RESOLVED] Select Max of one field based on another

[rasinc]

I am currently working on a Crystal 8.5 report that was working fine, until the client decided to make a change.

The scenario is simple. Pull the customer names and sales order numbers for any sales order entered within a date range. There are other fields involved but they are all in the one table.

Complication: I have no control over the database. The database is updated from a program that will take a sales order and if it is adjusted, it will duplicate the records, incrementing the ID number and marking the old one as cleared (without deleting it) and leave the new one visible in the system. The sales order number will be the same for both records. Oh, and if you fill a sales order and turn it completely into an invoice, it will also be marked as cleared.

So I am trying to find an SQL statement that will pull sales orders within a date range but if there are two records for the same sales order number for the same customer, I only want the one with the largest ID number.

Data
ID     SO#     Client
10     555      ABC Company
11     231      EFG Company
12     555      ABC Company
13     560      ABC Company
14     998      XYZ Company
15     222      DEF Company

So I want ultimately the following data only, sorted by customer and SO#. Note ID 10 was removed because it was edited, which created ID 12. So 12 is the latest version I want.

Data
ID     SO#     Client
12     555      ABC Company
13     560      ABC Company
15     222      DEF Company
11     231      EFG Company
14     998      XYZ Company

I know in Crystal I can just pull them all, group on SO#, sort descending and if the ID changes, conditionally suppress the printing. However, they have decided it might be nice to have the information directly in Excel and the Excel export from Crystal 8.5 is not quite as accurate as we would like. So I am trying to figure out the best way to handle this with one statement without going through the trouble of automating Excel to pull the data. I was thinking of using MSQuery.

So can someone point me to a SQL statement that will pull the data so I only get the highest ID for each sales order number?

TIA, rasinc

[szlamany]

Simple...

Select Id, SO#, Client
   From SomeTable S1
   Where S1.ID=(Select Max(S2.ID) From SomeTable S2
                                    Where S2.SO#=S1.SO#)

You basically gave yourself the answer in your own question!

...so I only get the highest ID for each sales order number?

It's sick that I take regular english statements and turn them into set-based logic - I've been doing this way too long

[rasinc]

Simple...

You basically gave yourself the answer in your own question!

It's sick that I take regular english statements and turn them into set-based logic - I've been doing this way too long

Simple for you maybe. I've been accused of being able to take set-based logic and turning it into Enlgish so real people can understand it. Not sure if it's true all the time for me, but who cares. And I don't care if you are sick, because it looks like you have come to my rescue. I still have to do some minor modifications and validate but I think you have given me the answer. Thanks.

Hadn't thought of a nested statement (I've never done them before but have read about them), I had tried the Max function on the main select statement but it didn't seem to pull the correct information so I knew I was missing something.