Sorry, this should be: SELECT address, errorMess, MIN(errorTime) as firstErrorTime FROM table WHERE .... GROUP BY address, errorMess
I didn't realize you already had 'address' in you main select. Dharmendar Kumar http://www.realmagnet.com -------------------------------------------------- From: "DK" <[email protected]> Sent: Wednesday, December 08, 2010 1:17 PM To: "sql" <[email protected]> Subject: Re: DISTINCT clause on certain columns only > > This should do it: > > SELECT address, errorMess, MIN(errorTime) as firstErrorTime, > MAX(address) -- > or MIN(address) > FROM table > WHERE .... > GROUP BY address, errorMess > > Dharmendar Kumar > http://www.realmagnet.com > > > -------------------------------------------------- > From: "George Gallen" <[email protected]> > Sent: Wednesday, December 08, 2010 1:07 PM > To: "sql" <[email protected]> > Subject: RE: DISTINCT clause on certain columns only > >> >> What if you used a sub query in place of the field? and had that subquery >> just be a DISTICT selection. >> >> George >> >>> To: [email protected] >>> Subject: Re: DISTINCT clause on certain columns only >>> Date: Wed, 8 Dec 2010 09:06:08 -0800 >>> From: [email protected] >>> >>> >>> 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:3375 Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/sql/unsubscribe.cfm
