Solved

using distinct keyword in SQL on Access DB

Posted on 2006-11-13
8
450 Views
Last Modified: 2008-02-01
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.
 
0
Comment
Question by:samic400
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 77

Accepted Solution

by:
peter57r earned 200 total points
ID: 17931705
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
 
LVL 26

Assisted Solution

by:jerryb30
jerryb30 earned 50 total points
ID: 17931727
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
 
LVL 26

Expert Comment

by:jerryb30
ID: 17931730
hmm, should have refreshed.
0
 
LVL 13

Author Comment

by:samic400
ID: 17931781
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 6

Expert Comment

by:gvlob
ID: 17931904
Use peter57r's solution except change "max(autoid) as maxid" to "first(autoid) as firstid"
0
 
LVL 6

Expert Comment

by:gvlob
ID: 17931919
Oops, I should have refreshed also. Jerryb30 will work as is.
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 17932011
lol. :>)
My choice of aggregation was dumb luck.  Peter answered as well as anyyone could, given information provided in original question.  
0
 
LVL 13

Author Comment

by:samic400
ID: 17932603
thank you for the help.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now