[RESOLVED] Select Max of one field based on another
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.
Code:
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.
Code:
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
Re: Select Max of one field based on another
Simple...
Code:
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!
Quote:
Originally Posted by
rasinc
...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 ;)
Re: Select Max of one field based on another
Quote:
Originally Posted by
szlamany
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.
