Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

using distinct keyword in SQL on Access DB

Posted on 2006-11-13
8
477 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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
 
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

808 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