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(DispatchNum ber) 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 uniqueidentifie r

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(repeti tion). 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(DispatchNum ber) 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(DispatchNum ber)? I dunno....