distinct always applies to the entire record set, so if you return 2 fields the two fields together will be distinct. In this case it looks like you want to get rid of distinct and put: group by transaction_user_id at the end. Since you already have MAX(transaction_date), it will work as long as you remove the ,* . The problem with * is that sql won't know what to return for the other fields, since it only has one row to put it in, it needs to know what value ie.. max(), avg(), last() ... so on... you'll need to use an aggregate function for any fields you want to include. Does that help? <>< Ryan -----Original Message----- From: Matthew Moldvan [mailto:mmoldvan@trilogyintl.com] Sent: Monday, July 07, 2003 1:18 PM To: 'Sparky Kopetzky'; PHP DB Group Subject: RE: Very confused with query Hi Robin, I think you are not using DISTINCT correctly ... try SELECT DISTINCT whatever instead of the ()s. Also, the use of * and DISTINCT together really makes no sense ... in the SQL, you are saying select only one record, then select everything, which are opposites. Don't mean to be rude, but I recommend picking up a good book on database theory. :) Regards, Matthew Moldvan System Administrator Trilogy International, Inc. -----Original Message----- From: Sparky Kopetzky [mailto:sparkyk@blackmesa-isp.net] Sent: Saturday, July 05, 2003 12:20 PM To: PHP DB Group Subject: Very confused with query Good morning!! I'm trying to select 1 and only one record from each user based on the last date from a transaction file. This is what I'm using and it's not even close. It seems DISTINCT does not work!! SELECT DISCTINCT(transaction_user_id), MAX(transaction_date), * FROM transaction; Could some kind soul help me figure this one out?? Robin E. Kopetzky Black Mesa Computers/Internet Services www.blackmesa-isp.net -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
