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" <g_gal...@hotmail.com> Sent: Wednesday, December 08, 2010 1:07 PM To: "sql" <sql@houseoffusion.com> 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: 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:3374 Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/sql/unsubscribe.cfm
