using distinct keyword in SQL on Access DB

I have to use the distinct keyword against three fields in my Access DB. I can do this fine but I need to be able to retain a field called "autoid", which is an auto number because I need to know what record number this is. How could I code this in SQL to get the distinct values from the 3 fields put also keep the autoid field as well?

TIA.
 
LVL 13
samic400Asked:
Who is Participating?
 
peter57rCommented:
Hi samic400,
If you have 3 records with the values for 3 fields but different autoid values, how do you decide which autoid value to keep?

One solution

Select field1, field2, field3, max(autoid) as maxid from mytable
Group by field1, field2, field3

Pete
0
 
jerryb30Commented:
Your DISTINCT requirement indicates that you could have more than 1 autoid associated with the three fields.
So which autoid woulld you use?


select first(autoid) as identity, field1, field2, field3 from yourtable group by field1, field2, field3

0
 
jerryb30Commented:
hmm, should have refreshed.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
samic400Author Commented:
I want the autoid data from the first record that was unique because I will be using this number to go after something else.

I just want to eliminate all duplicate records that is why I am using the distinct keyword on the three fields but I want to know what the autoid field holds so I can do something else with it.
0
 
George LobCommented:
Use peter57r's solution except change "max(autoid) as maxid" to "first(autoid) as firstid"
0
 
George LobCommented:
Oops, I should have refreshed also. Jerryb30 will work as is.
0
 
jerryb30Commented:
lol. :>)
My choice of aggregation was dumb luck.  Peter answered as well as anyyone could, given information provided in original question.  
0
 
samic400Author Commented:
thank you for the help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.