Link to home
Start Free TrialLog in
Avatar of Brandon Garnett
Brandon Garnett

asked on

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.
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
Avatar of Brandon Garnett
Brandon Garnett

ASKER

I've requested that this question be deleted for the following reason:

I found a way around the issue I was having
I have to disagree with the deletion, without concrete feedback and/or further questions to the experts...
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.
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
The Min Max solution worked
Closed, 500 points refunded.
eenookami
CS Moderator