selecting distinct rows

Question

I am trying to select distinct but its not working

SELECT  Distinct `table1`.`myID` AS `myID`,  
                 `table1`.`TypeID` AS `TypeID`,  

Is there any thing I am missing?

Answer 1

Distinct doesn't work on a single field, it works on the entire row. Your result contains only the distinct rows, i.e. only rows where all fields are the same are removed.

If you want distinct values for a specific field, you can use group by:

select table1.myID, min(table1.TypeID) as TypeId
from ...
group by table1.myID

This will give you a result with distinct values for myID, but for the fields that you are not grouping on you should specify which value to get. For this you use aggregates like min and max. MySQL might allow you specify fields without aggregates, but then it will just pick the first value that comes up (which might not at all be what you think would be the first value).

Answer 2

One way to do what you seem to want is:

SELECT  Distinct 'myID' as IDType, `table1`.`myID` AS ID
UNION
SELECT DISTINCT 'TypeID' as IDType, `table1`.`TypeID` AS `ID`

This will give you unique values for each column as follows:

myID    1
myID    2
myID    3
TypeID  101
TypeID  201
TypeID  301

You won't know which values go together, but you will know which values show up in each column.

Answer 3

You cannot use distinct on a column only rows.

You can accomplish what you are trying to do by using subquery.