[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 108
  • Last Modified:

How do I Select only ONE of each item in my sql query?

I am selecting Distinct records from a query but in some cases it will pull the same record because something in the query is different. For example I am running a query to pull company names, the city they are in and the state they are in. The company has a unique ID and I want to select Distinct ID's not full records. The query returns the correct information but if the company has two addresses, then it will return the company twice. I want only one address to be shown. Each address has an Address Type, but if I limit it to a type, I do not get all the companies.
0
rcimasi
Asked:
rcimasi
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
 
PortletPaulCommented:
Your understanding of "distinct" is wrong I'm afraid.

"SELECT DISTINCT" is a "row operator",  meaning it decides "distinctiveness" over the whole row!!!

If you are using "SELECT DISTINCT" and then claiming it is returning duplicates, then you are using the wrong technique.

see: Select Distinct is returning duplicates ...

As I try to explain in that article, you have to decide what to do with the addresses; select distinct can't magically do it for you.

Guy's article (url above) shows some of the available techniques - but not all dbms platforms offer some of the solutions. For example Access does not offer the ROW_NUMBER() function.
0
 
rcimasiAuthor Commented:
I've requested that this question be deleted for the following reason:

I found a way around the issue I was having
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I have to disagree with the deletion, without concrete feedback and/or further questions to the experts...
0
 
rcimasiAuthor Commented:
By using the min and max in my SQL query on address items I was able to select the items I wanted in specific columns into one row.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
and min() / max() techniques are explained in both articles...
hence, you should accept them as the answers, being tutorials giving you the general path to the solution you requested.
the "distinct" for queries is such a general and simple issue, you can learn that easily, which is why I had written the article
0
 
rcimasiAuthor Commented:
The Min Max solution worked
0
 
Rob Jurd (eenookami)Community AdvisorCommented:
Closed, 500 points refunded.
eenookami
CS Moderator
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now