having problems getting 1 result returned in max query

[dp]

Hi everyone:

I am having a hard time trying to get my query to work. I only want to
return the maximum record from group of records that all have the same sales
order ID. I have an autonumber field in it that I can use to get the maximum
from this group. But, my query keeps returning multiple results.

This is what I have so far

SELECT (T1.Customer_ID) AS CustOldValue,T1.LogDate,T1.ComputerName
FROM (
SELECT Max(SALESORDERREF_TXNID) AS MaxOfSALESORDERREF_TXNID, Max(autoNum) AS
MaxOfautoNum
FROM customer_log_table
WHERE (SALESORDERREF_TXNID)='3340') as T2
inner join customer_log_table T1 on (T2.MaxOfSALESORDERREF_TXNID =
T1.SALESORDERREF_TXNID);

Any help would be appreciated,
Thanks,
David

 

[MGFoster]

Hi everyone:

I am having a hard time trying to get my query to work. I only want to
return the maximum record from group of records that all have the same sales
order ID. I have an autonumber field in it that I can use to get the maximum
from this group. But, my query keeps returning multiple results.

This is what I have so far

SELECT (T1.Customer_ID) AS CustOldValue,T1.LogDate,T1.ComputerName
FROM (
SELECT Max(SALESORDERREF_TXNID) AS MaxOfSALESORDERREF_TXNID, Max(autoNum) AS
MaxOfautoNum
FROM customer_log_table
WHERE (SALESORDERREF_TXNID)='3340') as T2
inner join customer_log_table T1 on (T2.MaxOfSALESORDERREF_TXNID =
T1.SALESORDERREF_TXNID);

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

What do you mean by "maximum record"? Maximum of what - date, sales
amount? Using the autonumber is wrong, 'cuz of UPDATEs may change
whatever it is you consider the "maximum" value.

Here's how to do it, if you were using the log date:

SELECT Customer_ID, LogDate, ComputerName
FROM customer_log_table
WHERE LogDate = (SELECT MAX(LogDate) FROM customer_log_table
WHERE SalesOrderRef_TxnID = '3340')
AND SalesOrderRef_TxnID = '3340'

If you have more than one record w/ the same log date for the
Transaction ID 3340, you'll get multiple records in the result.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQyilGoechKqOuFEgEQILEgCfTX6ViwVah94u+pANdoaSjOHotgYAn1mf
0YmfXrSKB1W6qkJFBL0EziI2
=q5bG
-----END PGP SIGNATURE-----

 

[dp]

yes,yes yes, love ya

thats exactly what I was looking for. Sometimes I overcomplicate things...