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:[EMAIL PROTECTED]
Sent: Monday, July 07, 2003 1:18 PM
To: 'Sparky Kopetzky'; PHP DB Group
Subject: RE: [PHP-DB] 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:[EMAIL PROTECTED]
Sent: Saturday, July 05, 2003 12:20 PM
To: PHP DB Group
Subject: [PHP-DB] 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

Reply via email to