how do i apply the the distinct keyword in mysql so that it is only checking that one column field is unique, while still returning other columns from my table?
A:
SELECT DISTINCT Column_1, Column_2, Column_3 FROM Table
You may, however, want to elaborate on your question as it seems a little too straight forward for my answer to be THE answer.
When you lead the column name with DISTINCT it's only checking on that column. Am I misunderstanding the question?
jerebear
2009-04-09 04:17:21
that is what i am currently doing, that will check every field to ensure it is distinct. i only want to check one field.
Eric
2009-04-09 04:19:06
You can use DISTINCT and still get duplicate field results from other columns. What are the results of your efforts that are precluding the use of the above statement?
jerebear
2009-04-09 04:22:38
The above query does only check for DISTINCT values in Column_1
jerebear
2009-04-09 04:23:34
I think the above query will check for a distinct set of all columns. If it was only checking Column_1, how would it know which record to select where Column_1 is distinct, but the other columns aren't?
Andy White
2009-04-09 04:25:20
trying to split a voterfile that has household info in each row, into a voters table and household table. some voters have the same household_id but the address is slightly different (i.e. typos) but the data is close enough, so i just want it to select distinct IDs and copy that data to the table.
Eric
2009-04-09 04:28:20
Andy White, I pulled example data sets from my database using the above query and it confirmed that yes, indeed, it only pulled the unique fields in column_1
jerebear
2009-04-09 05:25:08
No, I think your test data is just bad. Create a table with Col1, Col2, Col3 (all ints), and insert records like this: [1,2,3], [1,4,5], [2,3,4], [2,4,5], [3,1,2]. If it's only pulling distinct Col1 records, how would it know which record to choose between [1,2,3] and [1,4,5]... etc.
Andy White
2009-04-09 07:08:54
That's what I'm saying. It would pull the first distinct record with Col1 value of "1" but ignore needing a distinct Col2 and Col3 value
jerebear
2009-04-09 07:17:48
It doesn't need to know which record to pull according to the OP's requirements. I've re-read this question several times and I don't see why I'm off on this one.
jerebear
2009-04-09 07:18:33
Maybe I'm not understanding what you're saying, but the point I'm trying to make is that "distinct" is applied across all columns, not just the first column. The example I gave actually returns all the records listed (incl. both [1,2,3] and [1,4,5]), since they are distinct across all columns.
Andy White
2009-04-09 07:25:03
I just re-read the question too, I think it's just not worded very well.
Andy White
2009-04-09 07:26:17
Sorry for the confusion using groupby works fine.i just need to make sure only one record has that household_id as it is becoming the PK in a new table that my query is populating. even though the data varies in small ways it is acceptable for my purposes.
Eric
2009-04-09 17:15:42
One definite misunderstanding here: DISTINCT is not a modifier for a column, it's a modifier for the query, i.e. you can't do SELECT a, DISTINCT b, DISTINCT c, d, e FROM ... - you do a SELECT DISTINCT query.
soulmerge
2009-04-10 07:22:07
+3
A:
You'll need to use group by instead.
SELECT SUM(A), MIN(B), C FROM TABLE WHERE A > 1 GROUP BY C;
Note that if you're grouping on one column and returning others you have to provide some sort of way to cram all those values in the other fields into one. That way is called an aggregate function, and you'll have to check the manual for your database version to know exactly what your options are. See http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html for mysql.
easel
2009-04-09 04:18:09
+4
A:
For being able to do that, mysql must know what to do with the other columns. You GROUP BY the column that should be unique and use a function that will tell it what to do with the others (a so-called aggregate function). MAX() and COUNT() are common examples:
SELECT studentId, COUNT(courseId) AS AmountEnrolledCourses
FROM student_enrollment
GROUP BY studentId
SELECT athlete, MAX(distance) AS PersonalRecord
FROM longjump
GROUP BY athlete
soulmerge
2009-04-09 04:19:26