What if you used a sub query in place of the field? and had that subquery just be a DISTICT selection. George > To: sql@houseoffusion.com > Subject: Re: DISTINCT clause on certain columns only > Date: Wed, 8 Dec 2010 09:06:08 -0800 > From: br...@electricedgesystems.com > > > Hey Claude, > > It sounds like you want a single record when an address has multiple > attempts.....so that is what I'm basing my assumptions on.... > > 1) I don't think this is possible. > You can get just one record per address, but you'd have to loop over > that output and lookup the error time and messages for each address (not > efficient but would get you what I think you're after). > > 2) You could play with the "GROUP BY" clause, but you'll still end up > with a record for every time/message combo for a given address > > SELECT address, errorMess, errorTime > GROUP BY address, errorMess, errorTime > > If you find a solution...be sure to post it ;-) > > Cheers > > > On Wed, 2010-12-08 at 11:51 -0500, =?ISO-8859-1?Q?Claude_Schn=E9egans > wrote: > > > Hi, > > > > It happens often that I'd need to have a DISTINCT clause applied to some > > columns only, instead of the whole row. > > Example: > > I have a request of rejected messages with a date-time column and the > > addess in another one. > > Sometimes, the sever attempted to send the messages many times to the same > > address. > > I'm not interest of having the time for every attempts, only the first one. > > Ex: SELECT DISTINCT errorTime, address, errorMess > > What I need who be kind of: SELECT DISTINCT(address, errorMess) errorTime, > > address, errorMess > > > > Any idea on how to do this in plain SQL ? (The database is Access) > > > > > >
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3373 Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/sql/unsubscribe.cfm
