Get the greater of the numbers in sql seelect statement

[sasasasa]

I have a select statement

SELECT C.CustomerID, D.DispatchNumber
FROM tblCustomer C LEFT JOIN tblDispatch D ON C.CustomerID = D.CustomerID
WHERE D.DispatchNumber ????(is bigger)

I don't know what to write in the statement to get the bigger dispatch number to show with customer ID because there are repetitions for dispatch number for a particular customer. I don't need the Max(DispatchNumber) because it gives me the highest of all the dispatch numbers. How can I get the highest of the dispatch number for a customer.
My Dispatch table looks like:

CustomerID         DispatchID
---------------------------------
1                    1
---------------------------------
1                    2
---------------------------------
2                    1
---------------------------------
2                    2
---------------------------------
2                    3
---------------------------------
3                    1

[Oralloy]

Try

SELECT C.CustomerID, MAX(D.DispatchNumber)
  FROM tblCustomer C LEFT JOIN tblDispatch D ON C.CustomerID = D.CustomerID
GROUP BY C.CustomerID
 

[sasasasa]

Thanks, it works.

[Oralloy]

Glad to help.

[sasasasa]

I am trying to add one more field which is a uniqueidentifier

SELECT C.CustomerID, MAX(D.DispatchNumber), D.DispatchID
  FROM tblCustomer C LEFT JOIN tblDispatch D ON C.CustomerID = D.CustomerID 
GROUP BY C.CustomerID

and again all the rows are showing up for that customer(repetition). How can I make it show the row that has Max Dispatch Number.I just want to see only 1 row with the DispatchID.

[Oralloy]

Sorry for the delay, I've been sick the last five days. Ugh!

Is D.DispatchID unique? If it is, you can use the MAX function on it, too:

 
SELECT C.CustomerID, MAX(D.DispatchNumber), MAX(D.DispatchID) 
  FROM tblCustomer C LEFT JOIN tblDispatch D ON C.CustomerID = D.CustomerID 
GROUP BY C.CustomerID 
 

If it's not unique, you might want to do something like this:

 
SELECT C.CustomerID, D.DispatchID, MAX(D.DispatchNumber)
  FROM tblCustomer C LEFT JOIN tblDispatch D ON C.CustomerID = D.CustomerID 
GROUP BY C.CustomerID, D.DispatchID 
 

[NeoPa]

Oralloy, I don't think that does it, as the record with the max(DispatchNumber) may be different from the one with the Max(DispatchID).

The real difficulty is that the question is asked so poorly. Even now, after a bit of prompting, the question still isn't clear (and the example data quite useless). If we can get a clear question then I'm sure we can provide a suitable answer.

[Oralloy]

I agree. In retrospect, I may very well have misunderstood the requirement.

Perhaps he wants the tbdDispatch records associated with the MAX(DispatchNumber)? I dunno....