DISTINCT clause on certain columns only

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)

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: ----- Excess quoted text cut - see Original Post for more -----

What if you used a sub query in place of the field? and had that subquery just be a DISTICT selection. George ----- Excess quoted text cut - see Original Post for more -----

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 ----- Excess quoted text cut - see Original Post for more -----

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 ----- Excess quoted text cut - see Original Post for more -----

>>It sounds like you want a single record when an address has multiple attempts. Exactly. Some errors are permanent and the server won't retry, some are not and the server will retry many time. In that case I'm only interested in the time of the first attempt, since it corresponds the time the application sent all messages.