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