Can't get unique values

Question

I'm using my sql to get unique values from database

My query looks as follows, but somehow I'm not able to get unique results

                    SELECT
   DISTINCT(company_name),
   sp_id    
  FROM
   Student_Training
  ORDER BY
   company_name

sp_id is the primary key, and then company_name is the companies name that needs to be unique

looks as follows

sp_id, company_name
1      comp1
2      comp2
3      comp2
4      comp3

Just not sorting this unique

Answer 1

Which id you want in case when a single company_name has two or more id's?

This:

SELECT  DISTINCT company_name                 
FROM    Student_Training

will select only the company_name.

This:

SELECT  company_name, MIN(id)
FROM    Student_Training
GROUP BY
        company_name

will select minimal id for each company name.

Answer 2

DISTINCT will make unique rows, meaning the unique combination of the field values in your query.

The following query will return a list of all the unique company_name together with the first match for sp_id.

SELECT sp_id, company_name
FROM Student_Training
GROUP BY company_name

And as Arthur Reutenauer has suggested, it is indeed pretty deceiving that MySQL allows the DISTINCT(fieldname) syntax when it actually means DISTINCT(field1, field2, ..., fieldn)

Answer 3

DISTINCT does not work the way you think it does. It gives you a distinct record. Stop and think about how it could return what you expect it to return. If you had a table as follows

id    name
1     joe
2     joe
3     james

If it only returned distinct names, which id would it return for joe?

You may want

SELECT company_name, Min(sp_id) FROM Student_Tracking GROUP BY company_name

or perhaps (as above) just

SELECT DISTINCT company_name from student_training

Answer 4

DISTINCT works globally, on all the columns you SELECT. Here you're getting distinct pairs of (sp_id, company_name) values, but the individual values of each column may show duplicates.

That being said, it's extremely deceiving that MySQL authorizes the syntax SELECT DISTINCT(company_name), sp_id when it really means SELECT DISTINCT(company_name, sp_id). You don't need the parentheses at all, by the way.

Edit

Actually there's a reason why DISTINCT(company_name), sp_id is valid syntax: adding parentheses around an expression is always legal although it can be overkill: company_name is the same as (company_name) or even (((company_name))). Hence what that piece of SQL means is really: “DISTINCT [company_name in parentheses], [sp_id]”. The parentheses are attached to the column name, not the DISTINCT keyword, which, unlike aggregate function names, for example, does not need parentheses (AVG sp_id is not legal even if unambiguous for a human reader, it's always AVG(sp_id).)

For that matter, you could write SELECT DISTINCT company_name, (sp_id) or SELECT DISTINCT (company_name), (sp_id), it's exactly the same as the plain syntax without parentheses. Putting the list of columns inside parentheses – (company_name, sp_id) – is not legal SQL syntax, though, you can only SELECT “plain” lists of columns, unparenthesized (the form's spell-checker tells me this last expression is not an English word but I don't care. It's Friday afternoon after all).

Therefore, any database engine should accept this confusing syntax :-(