Larry Vollmer
asked on
MySQL Distinct question
I have a question about the "distinct" syntax.
In this example:
I only want the distinct pID.
In this query, would my results show distinct values for pID, name, address, phone, and email or JUST pID?
In this example:
Select distinct pID, name, address, phone, email from table;
I only want the distinct pID.
In this query, would my results show distinct values for pID, name, address, phone, and email or JUST pID?
ASKER
Here is my real life query:
This works fine, except that it returns rows multiple times for pd.person_id. I am not sure how to apply what you suggested to make sure ONLY pd.person_id is distinct. Any idea?
SELECT
pd.person_id, pd.first,pd.email,sod.option_text
FROM
pd
INNER JOIN
srf ON pd.person_key = srf.person_key
INNER JOIN
ON srf.survey_option_key = sod.survey_option_key
WHERE
srf.survey_option_key IN (297,298,299)
AND
srf.survey_question_key = '43'
AND
pd.is_subscriber ='1'
ORDER BY
pd.person_id ASC
This works fine, except that it returns rows multiple times for pd.person_id. I am not sure how to apply what you suggested to make sure ONLY pd.person_id is distinct. Any idea?
This will do an inner join to just the distinct ID list so they only display once.
SELECT
pd.person_id, pd.first,pd.email,sod.option_text
FROM
pd
INNER JOIN
srf ON pd.person_key = srf.person_key
INNER JOIN
ON srf.survey_option_key = sod.survey_option_key
INNER JOIN (SELECT DISTINCT Person_id FROM PD) PDList ON pd.Person_ID = PDList.Person_id
WHERE
srf.survey_option_key IN (297,298,299)
AND
srf.survey_question_key = '43'
AND
pd.is_subscriber ='1'
ORDER BY
pd.person_id ASC
ASKER
thanks - it is running, so i will report back when finished.
Hi lvollmer,
Isn't this enough:
Select pID, name, address, phone, email from table group by pID, name, address, phone, email
Isn't this enough:
Select pID, name, address, phone, email from table group by pID, name, address, phone, email
ASKER
Murphey - woudln't that still show multiple rows for one pID?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I will run it now and test.
In the real query I am running (see second query) the result set will return multiple rows for pd.person_id if they have values for this:
WHERE
srf.survey_option_key IN (297,298,299)
In the real query I am running (see second query) the result set will return multiple rows for pd.person_id if they have values for this:
WHERE
srf.survey_option_key IN (297,298,299)
ASKER
this worked, thank you. I guess I did not understand how GROUP BY worked, and it is perfect for this instance.
GROUP BY wil select each record that has a unique group by,
if your tabel contains 2 rows with the same pID for 1 person with 2 adresses, the line is shown twice
if your tabel contains 2 rows with the same pID for 1 person with 2 adresses, the line is shown twice
If you want to just see the unique PID, you need to just have the columns that you want to be distinct in the select. If you need to join it back to the source table, you can make it a derived table and join it.