Hi Dina, I tried to reply to you directly, but the message was bounced - apparently my upstream ISP has been blocked by yours.
Everyone else please excuse the following message! Kay. -----Original Message----- From: Kay Smoljak [mailto:[EMAIL PROTECTED]] Sent: Friday, 3 May 2002 9:23 AM To: 'Dina Hess' Subject: RE: sql distinct problem Hi Dina, Thanks for all your help... looking at the scripts you are writing is making my toes curl, and I'm sure this is simple stuff for you! I think I saw a post from you on the Fusebox list the other day - if you ever have any Fusebox problems I'd be pleased to help in any way I can. I'll explain a little about the app - it's for a subscription site. Every couple of days the site admin will go through and run the credit card numbers for all the subscribers that expire in the next seven days. The query you helped me with the other day was for a page which lists all the subscribers that are due or overdue and have been run at least once, with unsucessful results. The WebUserArchive table is added to whenever a subscriber edits their own details - the old settings are saved in this table. The reason I wanted the last date from this table is so that the admin can look at the list of subscribers that need re-processing, and see the last date the details were updated - obviously if the subscriber has entered a new credit card expiry since the last time the admin attempted the transaction, the admin knows that they can try that subscriber again. I don't want to remove the old WebUserArchive table when a new update is submitted, because these guys are paranoid about fraud and they want to know *exactly* what the subscribers are up to. So I don't think it's denormalized too much - maybe it's not the best way to do it, but I don't think it's too bad. Of course I'm probably wrong :) Anyway, if it's going to be too difficult - and it's looking that way - I'll probably change the display so that instead of listing the last updated date, it will just display a link to display *all* the corresponding records from the WebUserArchive table. Thanks again for all your help, Kay. -----Original Message----- From: Dina Hess [mailto:[EMAIL PROTECTED]] Sent: Friday, 3 May 2002 6:27 AM To: [EMAIL PROTECTED] Subject: sql distinct problem kay, i forgot to mention that distinct will not work because it will only eliminate duplicate column values; that is, if web user number one has two records in the webuserarchive table and the value of the dtupdated field in each of those records equals '2002/1/15,' then distinct would work perfectly to pull just one of those values into a single record in the result set. however, in this case, the value of dtupdated is different in each record for the same user, so the distinct function is doing exactly what you asked it to do. it is returning each record with a distinctly different value in the dtupdated field. the only way (i know of) that you can return just one of those records is to use either the min() or max() aggregate function on the dtupdated column in the select clause. but in this case, there's a caveat. this 'solution' to your multiple-records-per-user may break if the isdisabled field is a boolean datatype (as it appears to be). that's because you cannot group by a boolean data type column. add to that the fact that you cannot return an aggregate column value without first listing all non-aggregate columns in a group by clause, and we're back to trying to figure out a workaround for your multiple-records-per-user problem. at any rate, the real problem here is that your database design is failing you. it's not normalized. if it were normalized, you wouldn't have nine records in the same table for an archived web user. unfortunately, as this database grows, you will experience increasingly complex data manipulation problems with greater frequency. with that in mind, i'd be pleased to offer a few suggestions. if you'd like my help, simply let me know if you're interested. ~ dina ______________________________________________________________________ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
